VBA set variable from cell value

[Hector]

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

 

[phantom1975]

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.

 

[Nimrod]

Sub SetVarFromCell()
Dim FTW As Long

FTW = Worksheets("Sheet1").Cells(12, "F").Value
MsgBox (FTW)
End Sub

 

[Nimrod]

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

 

[Hector]

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

 

[Hector]

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

 

[mcgurrgurr]

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)?

 

[pullmyefinger]

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?

 

[pullmyefinger]

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!

 

[mesje]

Hello

I found this topic and tried to apply it to the following piece of code, that automates e-mail sending:
[TABLE="width: 211"]
<tbody>[TR]
[TD="class: xl64, width: 211"][/TD]
[/TR]
</tbody>[/TABLE]
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