PDA

View Full Version : [SOLVED] Variable reference cell value



btstlouis
12-11-2013, 10:08 AM
How do I apply the value of a specific cell to a variable? I want variable x to match the text in cell A1 on the first sheet of the workbook. I tried this, but nothing seems to be working.

Dim x As String
x = Worksheets(1).Range("A1").Value

I'm trying to avoid using ActiveSheets as much as possible, so do not include that in your answer. Additionally, what is the best way to check the current value of a variable?

sassora
12-11-2013, 03:53 PM
I'm not sure what specifically is happening when you say "not working". Is it an error message or that the code that follows doesn't seem to respond to it?

I'd assume that you are putting those lines into a sub (routine):

Sub test()

Dim x As String
x = Worksheets(1).Range("A1").Value


End Sub

This passes the value to x as intended. To view the value, you could use msgbox:

msgbox x

The value of x would appear in a message box.

OR
debug.print x
to put the output in the "Immediate window"

OR
View the "Watches" window, add a watch for "x" and then press F8 to run through the sub line by line and watch the value change as the value is assigned.

Bob Phillips
12-12-2013, 01:58 AM
I think you need to give a lot more context to your question. What you gave is perfectly good in isolation, but may be the opposite in the actual case, for instance are we talking about the active workbook, ThisWorkbook, the first, second,.. worksheet and so on.

Paul_Hossler
12-12-2013, 07:27 AM
"Not working" is a little ambigious

Wrong answer?
No answer?
Empty string?
Error?


This should be working ....



Sub test()
Dim x As String
Msgbox Worksheets(1).Name
x = Worksheets(1).Range("A1").Value
MsgBox x & " -- " & Len(x)
End Sub



... assuming that the correct WB is active and that whatever Worksheets(1) is, is really where the desired data is

If you have a small WB as an example, please post it

Personally, I rarely use a WS index the --- Worksheets(1).Range("A1") -- preferrring to use either the .Name -- Worksheets("UserData").Range("A1") -- or the Codename -- Sheet1.Range("A1")

Paul

btstlouis
12-31-2013, 12:12 PM
Thank you everyone for your help, and sorry for the late response. I was able to declare the variables correctly and check their values using F8 and the locals window (thanks, sassora). Here is what I have so far:


Sub Save()
Dim D32 As String
D32 = Worksheets("WorksheetNames").Range("B1")
Dim D34 As String
D34 = Worksheets("WorksheetNames").Range("B2")
End Sub

My next and final step is to have two of the worksheets in the workbook 'save as PDF', with the title matching the stored value of the 'D32' and 'D34' variables respectively. Can someone get me started with some code?

Thank you.

Bob Phillips
01-01-2014, 05:14 AM
The macro recorder is your friend


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Range("D32").Value & Range("D34").Value & " .pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

btstlouis
01-02-2014, 07:10 AM
Correct, but I don't know how to make the filename match the stored value of the variable (D32 or D34) using the macro recorder. I attempted to concatenate the file path with the value of the variable, but after I run the macro I get a notice saying it wasn't saved.

Here is the code I'm currently have. Can you see where the error might be?


Sheets("D32").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"U:\My Documents\Month End\" & Range("D32").Value & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

btstlouis
01-02-2014, 07:37 AM
I just figured it out. Here is the final result if anyone is interested. Thanks everyone for your help.


Sub SavePDF()
Dim D32 As String
D32 = Worksheets("WorksheetNames").Range("B1")
Dim D34 As String
D34 = Worksheets("WorksheetNames").Range("B2")

Sheets("D32").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"U:\My Documents\Month End\" & D32 & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

Sheets("D34").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"U:\My Documents\Month End\" & D34 & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

Sheets("WorksheetNames").Select
End Sub