Assigning string "AJ" to a variable in VBA is not working. why ?

[Navin8211]

Hello, I am trying to assign a strong "AJ" to a variable in VBA. After executing the code, I am getting an empty variable.

I am assigning like below,
Var1="AJ"

This AJ is actually referring to 'AJ' column in another tab of the Excel sheet. Please do provide some insights on how to tackle this problem. Thank you in advance.

 

[Skyybot]

Var1 = ThisWorkbook.Worksheets("MySheetName").Range("AJ").Value <---Replace MySheetName with the Name of your Sheet.

 

[Peter_SSs]

Welcome to the MrExcel board!

You may need to give us the whole code so that we might be able to see what is going wrong.

On the face of it, there is nothing wrong at all with the single line of code you provided. For example, here is that same line of code in a small procedure. Try copying this procedure to your workbook and run it. You should find that the variable Var1 is not empty.

Sub Test()
  Dim Var1 As String
  
  Var1 = "AJ"
  MsgBox Var1
End Sub

 

[meethed]

I'm having the same issue here.

Any string that begins with AJ specifically seems to return null.

Lowercase "aj" seems to be fine. But I can't even get UCase("ajm") to return a correct value and it is doing my head in!!

 

[Peter_SSs]

Welcome to the MrExcel board!

For a start, could we see you whole code, rather than just part of perhaps one line of it? My signature block below has help about posting code in the forum.

 

[Dennis S]

I am having the same issue. I cannot set a variable to the value of "AJ", but I can set it to "Aj" or "aj" or "aJ".
This is also the same issue with the ".Address" for column 36 . AJ is column 36 on the worksheet

Sub Test()
Dim TestValue as string

TestValue = "AJ" ' Shows nothing when I hover over and macro sees no value but does show in locals window
TestValue = "aj" ' Shows "aj" when I hover over, macro sees value and shows in locals window
TestValue = "Aj" ' Shows "Aj" when I hover over, macro sees value and shows in locals window
TestValue = "aJ" ' Shows "aJ" when I hover over, macro sees value and shows in locals window

' Also
Cells.(1,36).Address ' also has the Same issue. "AJ" is the 36th Column of a worksheet

End Sub

 

[dreid1011]

Not sure what your issue could be. I just tried and I am able to set a string to AJ.

Private Sub SetVar()
Dim Var1 As String
Var1 = "AJ"
MsgBox Var1
Var1 = Cells(1, 36).Address
MsgBox Var1
End Sub

 

[Dennis S]

Sure let me try and clear up. Hover over the actual variable you assigned to the value "AJ", it will show as "Var1 =" You will be able to see it in the msgbox, you will be able to see it in the locals window, but you wont see hovering and the macro cannot act on it. Note the exception being you can see it in DEBUG, Locals window, and MSGBOX

We have soft coded our formulas and build them dynamically based on the columns the time we are executing.

We pull the column alpha from the row/column

rowALPHA = SPLIT(Cells(1 ,36).Address, "$"( 1) ' the Column Alpha Id i.e. "AJ" to use in formulas.
rowALPHA has no value and the formula fails.

Every other value works with no issue. We have been using this routing for 2 years and this is the first time we are using column 36 in a formula

This issue has been reported many times since 2019 on various sites. In trying to find a solution we found that there is something about "AJ" that Excel VBA doesnt like.

Dennis

 

[dreid1011]

I was hovering over the actual variable, and the values are as I posted in my screenshots. How are you trying to use the variable to say "the macro cannot act on it" ?

What is rowALPHA in your example?

 

[Scott Huish]

Your syntax isn't quite right, but when I fixed that it worked fine for me:

Sub test()
rowALPHA = Split(Cells(1, 36).Address, "$")(1)
MsgBox rowALPHA
End Sub