How to get the value from a variable
Hi. I'm new to VBA and wrote this code. I assigned a variable to my case
statement but when I try to see what value is in that variable I get a 0. Question is how do I get the value from the variable and use it in my calculation later in the module? Here is the code. Thank you for the help. Sub retropay2() Dim paycode12 As Long Dim paycode13 As Integer Dim paycode1E As Integer Dim paycode1H As Integer Dim paycode1I As Integer Dim paycode1J As Integer Dim paycode1 As Double Worksheets("flx00012.tmp").Activate finalrow1 = Cells(65536, 6).End(xlUp).Row For j = 1 To finalrow1 'this will see if j,6 is equal to paycode 1,12,13 Select Case Cells(j, 6).Value Case 1 paycode 1=Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11) Case 12 paycode12 = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11) Case 13 paycode13 = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11) 'this will look at paycode 1E in j,6 Case "1E" paycode1E = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11) Case "1H" paycode1H = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11) End Select Next j End Sub Here's the code I'm using to see what's in the variable. It gives me a zero value. Sub SetVarFromCell() Dim paycode12 As Long MsgBox (ThisWorkbook.Name & " " & ActiveSheet.Name & " " & ActiveCell.Address) paycode12 = Worksheets("flx00012.tmp").Cells(18, "L").Value MsgBox (paycode12) End Sub |
How to get the value from a variable
Try these changes. Activecell is not set so it may return different values
each time. It is only a message and does not effect anything else in the code. You will still get the correct results. Sub retropay2() Dim paycode12 As Long Dim paycode13 As Integer Dim paycode1E As Integer Dim paycode1H As Integer Dim paycode1I As Integer Dim paycode1J As Integer Dim paycode1 As Double With ThisWorkbook.Worksheets("flx00012.tmp") finalrow1 = .Cells(Rows.Count, "F").End(xlUp).Row For j = 1 To finalrow1 'this will see if j,6 is equal to paycode 1,12,13 Select Case .Cells(j, 6).Value Case 1 paycode1 = .Cells(j, "G") * .Cells(j, "K") .Cells(j, "L").Formula = paycode1 Case 12 paycode12 = .Cells(j, "G") * .Cells(j, "K") .Cells(j, "L").Formula = paycode12 Case 13 paycode13 = .Cells(j, "G") * .Cells(j, "K") .Cells(j, "L").Formula = paycode13 'this will look at paycode 1E in j,6 Case "1E" paycode1E = .Cells(j, "G") * .Cells(j, "K") .Cells(j, "L").Formula = paycode13 Case "1H" paycode1H = .Cells(j, "G") * .Cells(j, "K") .Cells(j, "L").Formula = paycode1H End Select Next j End With End Sub Sub SetVarFromCell() Dim paycode12 As Long With ThisWorkbook.Worksheets("flx00012.tmp") MsgBox (ThisWorkbook.Name & " " & .Name & " " & _ .ActiveCell.Address) paycode12 = .Cells(18, "L").Value MsgBox (paycode12) End With End Sub "KevinM" wrote: Hi. I'm new to VBA and wrote this code. I assigned a variable to my case statement but when I try to see what value is in that variable I get a 0. Question is how do I get the value from the variable and use it in my calculation later in the module? Here is the code. Thank you for the help. Sub retropay2() Dim paycode12 As Long Dim paycode13 As Integer Dim paycode1E As Integer Dim paycode1H As Integer Dim paycode1I As Integer Dim paycode1J As Integer Dim paycode1 As Double Worksheets("flx00012.tmp").Activate finalrow1 = Cells(65536, 6).End(xlUp).Row For j = 1 To finalrow1 'this will see if j,6 is equal to paycode 1,12,13 Select Case Cells(j, 6).Value Case 1 paycode 1=Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11) Case 12 paycode12 = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11) Case 13 paycode13 = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11) 'this will look at paycode 1E in j,6 Case "1E" paycode1E = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11) Case "1H" paycode1H = Cells(j, 12).Formula = Cells(j, 7) * Cells(j, 11) End Select Next j End Sub Here's the code I'm using to see what's in the variable. It gives me a zero value. Sub SetVarFromCell() Dim paycode12 As Long MsgBox (ThisWorkbook.Name & " " & ActiveSheet.Name & " " & ActiveCell.Address) paycode12 = Worksheets("flx00012.tmp").Cells(18, "L").Value MsgBox (paycode12) End Sub |
All times are GMT +1. The time now is 12:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com