VBA refering to a cell for a piece of code

[cazdealer]

Hi,
I am working on a long macro. I'm not really good with VBA but so far, I'm impressed by what I've done (of course, with the help of the macro recorder and a lot of "google" code researches).


I would like to know if it's possible when you write your sub in VBA to refer to a cell for changing a string or a value in your code.
I don't want to always touch my VBA code to switch little values or names.
I would prefer to change them in cells in a worksheet named "info".

2 differents exemples I'd like to improve.



could I set up something like:

Dim filename as string
Dim upperbound as single

filename = Worksheets("info").Cells(1, 1).Value
upperbound = Worksheets("info").Cells(1, 2).Value


First, I would like to use "filename" to replace the word "Results" in the code line below.
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\bob\Desktop\Results " & Format(Date, "dd-mmm-yy") & ".xls", FileFormat _
:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


Second, in a part of a vba code starting the Solver, I would like to use "upperbound" to replace the value "0.15" in the code below

SolverOk SetCell:="$U$13", MaxMinVal:=1, ValueOf:=0, ByChange:="$U$5:$U$7", _
Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$U$5:$U$7", Relation:=1, FormulaText:="0"
SolverAdd CellRef:="$U$5:$U$7", Relation:=3, FormulaText:="0.15"



I know I cannot only switch "results" by "filename " and "0.15" by "upperbound"... but I guess there is a simple way to do it.
I found stuff using the "&" but I don't know how to apply this correctly.


thanks your help

 

[VoG]

Perhaps like this

ActiveWorkbook.SaveAs Filename:="C:\Users\bob\Desktop\" & Results & Format(Date, "dd-mmm-yy") & ".xls", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


SolverAdd CellRef:="$U$5:$U$7", Relation:=3, FormulaText:=CStr(upperbound)

 

[cazdealer]

Hi Peter,
thanks for the quick answer.
The second code is working perfectly!!

just something about the first one that I don't understand.

This is what I got before.
ActiveWorkbook.SaveAs Filename:= "C:\Users\bob\Desktop\Results " & Format(Date, "dd-mmm-yy") & ".xls", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

and this is what you've suggested to swich.
ActiveWorkbook.SaveAs Filename:= "C:\Users\bob\Desktop\" & Results & Format(Date, "dd-mmm-yy") & ".xls", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False



except the " &" before results, I don't understand how to put the "filename" for it to refer to the value in Worksheets("info").Cells(1, 1).Value. I guess it should be written somewhere in that code you provided.

thanks

 

[VoG]

You already defined filename

filename=Worksheets("info").Cells(1, 1).Value

. Clearly that will crash if the cell is empty.

 

[Ruddles]

ActiveWorkbook.SaveAs Filename:= "C:\Users\bob\Desktop\" & Results & Format(Date, "dd-mmm-yy") & ".xls", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

If your filename is in a variable called filename, it would be:-

ActiveWorkbook.SaveAs Filename:= "C:\Users\bob\Desktop\" & [B][COLOR=red]filename[/COLOR][/B] & Format(Date, "dd-mmm-yy") & ".xls",
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Is that what you mean?

 

[cazdealer]

Oups!!!
I should not have called it filename then... I didnt realize it was already used.

what about if I use this:

Dim nameofthedata as string
nameofthedata = Worksheets("info").Cells(1, 1).Value


could I write like "INDEX" in that cell.
to obtain "INDEX Results 13-Mar-11.xls" as the file name???

what would I need to change in this line to obtain that result? (how to include the "nameofthedata"

ActiveWorkbook.SaveAs Filename:= "C:\Users\bob\Desktop\Results " & Format(Date, "dd-mmm-yy") & ".xls", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


thanks

 

[cazdealer]

this is what I meant. It's working.
I've switched 'filename' to "nameofthedata" not to get confused.


thank Ruddle sand Peter

 

[Ruddles]

Oups!!!
I should not have called it filename then... I didnt realize it was already used.

You can call it filename if you wish.

could I write like "INDEX" in that cell.
to obtain "INDEX Results 13-Mar-11.xls" as the file name???

what would I need to change in this line to obtain that result? (how to include the "nameofthedata"

ActiveWorkbook.SaveAs Filename:= "C:\Users\bob\Desktop\" & nameofthedata _
        & "Results " & Format(Date, "dd-mmm-yy") & ".xls", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False