CODEWITHSUNDEEP
excelvba
excelguy
excelguy

Reputation: 1624

VBA, Use previous Column when it is stored as variable

I store my column in a variable. NameColumn is equal to Column AD.

How do I store a new variable to NameColumn - 1? I want to store AC.

full code:

Sub FindAfterShort()

    Dim nameColumn As String   ' Name Column Letter
    Dim ScoreColumn As String  ' Score Column Letter
    Sheets("Load check data").Activate
    With Rows(1)
        nameColumn = Split(.Find(Sheets("Input").Range("A2"), .Cells(.Cells.Count), xlValues, _
                xlWhole).Address, "$")(1)
        'PreviousColumn = NameColumn.Offset(0, -1)
    End With

    Debug.Print "Column Letters '" & nameColumn & "' and '" & PreviousColumn & "'."
    
    Dim k As Long
    k = Sheets("Load check data").Range("A1", Sheets("Load check data").Range("A1").End(xlDown)).Rows.Count
    Debug.Print (k)
    
    Sheets("Load check data").Range(nameColumn & "2:" & nameColumn & k).ClearContents
    Sheets("Load check data").Range(.Cells(2, nameColumn - 1), .Cells(k, nameColumn - 1)).Copy
end sub

I have trouble on this line:

`Sheets("Load check data").Range(.Cells(2, nameColumn - 1), .Cells(k, nameColumn - 1)).Copy`

Unqualified references on Cells

Upvotes: 0

Views: 116

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27249

Without changing too much original code, this should work well.

With Sheets("Load check data")

    Dim nameColumn as Long
    nameColumn = .Rows(1).Find(Sheets("Input").Range("A2"), .Cells(.Cells.Count), xlValues, xlWhole).Column

    .Range(.Cells(2,nameColumn- 1),.cells(k, nameColumn - 1)).Copy

End With

Upvotes: 2

v-c0de
v-c0de

Reputation: 140

When you say PreviousColumn, and if you are starting with AD, do you mean AC (you have written AE)?

If that's just a typo, how about something like this:

Add these function:

Private Function GetColNo(str_ColLetter As String) As Long

GetColNo = Range(str_ColLetter & 1).Column

End Function

Private Function GetColLetter(lng_ColNo As Long) As String

GetColLetter = Split(Cells(1, lng_ColNo).Address, "$")(1)

End Function

Then use them like this:

Dim NameColumn As String

Dim PreviousColumn As String

NameColumn = "AD"

PreviousColumn = GetColLetter(GetColNo(NameColumn) - 1)

Debug.Print (PreviousColumn)

This returns PreviousColumn = AC (since it's the column prior to AD)

Upvotes: 2

Related Questions