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
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
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
Display More
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: 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: 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/forums/deletin…es-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: 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: 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/excel-general/…atic-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