VBA set variable from cell value

Hector

New Member
Joined
Jan 13, 2003
Messages
16
It seems like this ought to be simple, but I can't figure out the correct syntax.

I have a worksheet (Sheet1) with a value of 1234 in cell F12. It is formatted as a number with 0 decimal places. I am trying to copy that value of 1234 into a variable. Here is my routine:

Sub SetVarFromCell()
Dim FTW As Long
Worksheets("Sheet1").Activate
FTW = Cells(12, "F").Value
End Sub


This routine runs without error, but FTW has a value of "0", rather than "1234" which is what it is supposed to have. I have also tried the statement:
FTW = Range("F12").Value and the result is the same -- "0" instead of "1234".

Is there a way to assign the value of the cell (1234) to the variable FTW?

Thanks,
Hector
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Your syntax is OK. I've tried all of your methods and can't reproduce the error you are having. Try running through your macro using the F8 key and see where the disconnection occurs.
 
Upvote 0
Sub SetVarFromCell()
Dim FTW As Long

FTW = Worksheets("Sheet1").Cells(12, "F").Value
MsgBox (FTW)
End Sub
 
Upvote 0
Your code work for me as well. I'm thinking you may not be accessing where you think you are. For example maybe you have several workbooks open.

Any way try this:
Select cell "F12" and run the following code : it might tell you something

Sub SetVarFromCell()
Dim FTW As Long

MsgBox (ThisWorkbook.Name & " " & ActiveSheet.Name & " " & actviecell.Address)
MsgBox (ActiveCell.Value)
FTW = Worksheets("Sheet1").Cells(12, "F").Value
MsgBox (FTW)
End Sub
 
Upvote 0
Thanks for your help! Nimrod, your second post identified my problem -- I had not been looking at the right worksheet. When I was testing I had multiple workbooks open and apparently at the time I was trying to run the simple macro, the active sheet was not the one with the 1234 in F12. As you who responded said, my code was working fine, but it was not pointing to the expected worksheet. Those are always sticky problems when the macro code is correct but some other factor is causing unexpected results. I will be more alert to that possibility in the future.
Thanks again, Hector
 
Upvote 0
Thanks for your help! Nimrod, your second post identified my problem -- I had not been looking at the right worksheet. When I was testing I had multiple workbooks open and apparently at the time I was trying to run the simple macro, the active sheet was not the one with the 1234 in F12. As you who responded said, my code was working fine, but it was not pointing to the expected worksheet. Those are always sticky problems when the macro code is correct but some other factor is causing unexpected results. I will be more alert to that possibility in the future.
Thanks again, Hector
 
Upvote 0
I just came across this thread. Is it possible to do this same process but instead of returning a numeric answer to return the contents of a cell with letters (AA, fort example)?
 
Upvote 0
lolol, I want to do the exact opposite of what is here. That is to store the value returned to a var from an inputbox(yadda, yadda, yadda) to the variable ASH. Then I want to dump the contents of ASH to a cell that will be part of a criteria range for a Filter..

Is there a function of some sort that will do this or do I have to play a bit?
 
Upvote 0
lolol, I want to do the exact opposite of what is here. That is to store the value returned to a var from an inputbox(yadda, yadda, yadda) to the variable ASH. Then I want to dump the contents of ASH to a cell that will be part of a criteria range for a Filter..

Is there a function of some sort that will do this or do I have to play a bit?

OK, got to play for a minute and it works. Thanks for the original idea!
 
Upvote 0
Hello :)

I found this topic and tried to apply it to the following piece of code, that automates e-mail sending:

<tbody>
</tbody>
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.to = Worksheets("SETUP").Cells(4, "C").Value
.CC = Worksheets("SETUP").Cells(5, "C").Value
.BCC = ""
.Subject = Worksheets("SETUP").Cells(6, "C").Value
.Body = "My text to receipients"
.Attachments.Add Destwb.FullName
'.Send
.Display
End With
On Error GoTo 0
.Close savechanges:=False
End With

Simply speaking I have a worksheet called SETUP in which I want to configure e-mail addresses of all receipients without allowing user to touch VBA.
For some reason I get no values assigned to OutMail.to , .cc & .Subject.

.Send doesn't work (since .to has no value, so I used .display for tests).
When I do .display it shows no value.

Would anyone have any idea what could have I missed here?

I ran out of ideas...

Thank you for your help,
mesje
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top