Posts by junho lee

Re: Check That Mandatory TextBoxes Have Filled

Hi Assaf,
Have a look at
http://www.ozgrid.com/VBA/control-loop.htm
Regards, junho

ADDED BY ADMIN

Private Sub CommandButton1_Click()


Dim cCont As Control


    For Each cCont In Me.Controls
        If TypeName(cCont) = "TextBox" And cCont = vbNullString Then
            MsgBox cCont.Name & " has not been filled in."
            cCont.SetFocus
            Exit For
        End If
     Next cCont
     
    'YOUR CODE HERE
End Sub

Re: Rolling Excel Solver

Hi joe,
Change

Range("B18:[COLOR=#ff0000][B]H18[/B][/COLOR]")

to

Range("B18", Range("B18").End(xlToRight))

regards, junho

Re: Rolling Excel Solver

Hi joe,
Yes your correct.
Regards, junho

Re: Rolling Excel Solver

sorry for late.
can you delete : [ , Engine:=1, EngineDesc:="GRG Nonlinear" ]
regards, junho

Re: Rolling Excel Solver

Hi joe,
In the VBE, Tools > References, make sure that Solver it ticked.
Regards, junho

Re: Rolling Excel Solver

Hi joe888,
try this, code from web.

Sub Macro2()
'check solver reference in Tools
    Dim SetCell As Range, ByChangingCell As Range, ValueCell As Range
    Dim rSetCell As Range, rUp As Range, rLow As Range
    Set rSetCell = Range("B16:H16")
    For Each SetCell In rSetCell
        With SetCell
            .GoalSeek Goal:=0, ChangingCell:=.Offset(-1)
        End With
    Next
    Set rSetCell = Range("B18:H18")
    For Each SetCell In rSetCell
        With SetCell
            .GoalSeek Goal:=0, ChangingCell:=.Offset(-1)
        End With
    Next
    Set rSetCell = Range("B13:H13")
    For Each SetCell In rSetCell
        Set ByChangingCell = SetCell.Offset(-3)
        Set rUp = SetCell.Offset(1)
        Set rLow = SetCell.Offset(6)
        SolverReset
        SolverAdd CellRef:=ByChangingCell.Address(True, True), Relation:=1, FormulaText:=rUp.Address(True, True)
        SolverAdd CellRef:=ByChangingCell.Address(True, True), Relation:=3, FormulaText:=rLow.Address(True, True)
        SolverOk SetCell:=SetCell.Address(True, True), _
                 ByChange:=ByChangingCell.Address(True, True), _
                 MaxMinVal:=3, ValueOf:=0, Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve userFinish:=True
        SolverFinish KeepFinal:=1
    Next
End Sub

regards, junho

Re: Sum 5 rows - question

Hi, Erin
Nice to meet you.
First, write =A2 anywhere blank columns and drag down. say, cell address 'e12'.
in cell F12=IF(A2="",B2+F11,B2), drag down.
Second move F12:F39 upward so that locate 'value 78 in cell F16' on the left of '1st April'.
You can do filter non zero for column E and copy&Paste.
Regards, junho

Re: Access and modify Data from Multiple Workbooks, Save modified data to new workshe

Hi, KNewbury nice to meet you.
First, turn on macro recoder. open file and work for that procedure then close file and stop recording.
Second, modify some of your recorded macro and add looping.
Regards, junho

Re: VBA user/password form to vary access

Hi clint182,
Nice to meet you in this forum. Please look at link below.
http://www.ozgrid.com/forum/showthread.php?t=73749&page=1
Regards, junho

Re: Replicate column formats on multiple sheets

hi, write like this.

select case ws.name
case "sheet1","sheet2","sheet3"
'
case "sheet4"
'
case else
'
end select

regards, junho

Re: Text Lookup then return value

Try this, confirmed 'ctrl+shift' key followed by enter.
=IFERROR(INDEX($H$1:$H$6,MATCH(FALSE,ISERROR(FIND($G$1:$G$6,B1)),0)),FALSE)

Re: Removing tick boxes from Web Data

Hi ICdeadppl,
For me this works.
http://www.pcreview.co.uk/foru…-check-boxes-t977997.html

Sub tester2()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If LCase(TypeName(obj.Object)) = "htmlcheckbox" Then
obj.Delete
End If
Next
End Sub

Regards, junho

Re: Replicate column formats on multiple sheets

Hi Mateous,
Nice to meet in Ozgrid.

Dim ws As Worksheet
For Each ws in ThisWorkbook.Worksheets
ws.Select
'copy your macro
Next

Regards, junho

Re: Change image based on combo box selection

Hi RevManager,
Try the following step.
First, locate picture L5, L6, L7 by company name.
Second, create name 'pic', formula:=INDEX('Group Data Entry'!$L$5:$L$11,'Group Data Entry'!$G$2)
Third, copy any cell followed by paste as 'linked picture' ( home tab - other paste option )
Last, change linked cell reference to 'pic' you named in 'Fomula Bar'.
Regards, junho

Re: VBA FormulaR1C1 w/ standard formula

Hi oldman33,
Nice to meet you.
This works.

ActiveCell.FormulaR1C1 = "=Sheet1!" & Range("e5").Address(False, False, xlR1C1) & "/rc[3]"

Regards, junho

Re: ADO Connection from Excel

Hi Ped,
Nice to meet you.
Here's the link which i searched.
http://www.vb-helper.com/howto_ado_use_access_password.html
Regards, junho

Re: Graphing a column range with 1 overlapping scatterpoint

Hi mfs1,
Nice to meet you.
I changed type to 'clustered column' chart.
Regards, junho

Re: Selecting a dynamic range

Hi Xenoun,
Is this a link what you are looking for?
http://www.excelforum.com/exce…efining-static-names.html
Regards, junho

Re: Set Data Validation list to ignore blanks in source list

Hi Juger,
row(a1) is a parameter of function 'small'.
SMALL(IF(ISNUMBER(SEARCH("Space Temp",Sheet1!$E$3:$E$20)),ROW(Sheet1!$E$3:$E$20),""),ROW(A1))
small(row(e3:e20) if cell is included "Space Temp",1)
so lowest value of rows having "Space Temp" in range Sheet1!$E$3:$E$20.
Regards, junho

Re: Set Data Validation list to ignore blanks in source list

Hi Juger,
Nice to meet you.
I use array formula and named range.
=IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH("Space Temp",Sheet1!$E$3:$E$20)),ROW(Sheet1!$E$3:$E$20),""),ROW(A1))-2),"",INDEX(Sheet1!$C$3:$C$20,SMALL(IF(ISNUMBER(SEARCH("Space Temp",Sheet1!$E$3:$E$20)),ROW(Sheet1!$E$3:$E$20),""),ROW(A1))-2))
And
=Sheet3!$A$4:INDEX(Sheet3!$A$5:$A$30,MATCH(LOOKUP(2,1/(Sheet3!$A$5:$A$30<>""),Sheet3!$A$5:$A$30),Sheet3!$A$5:$A$30,0))
Please look at attatched.
Regards, junho