CODEWITHSUNDEEP
excelvbadynamicrange
Yurii Gul
Yurii Gul

Reputation: 13

Dynamic Range in VBA

I am having hard times figuring out how to pass a dynamic range into the formula.

Usually I will have a scenario where I know in what column my range is going to be (example below), where LR is number of rows in my range.

Range("A1:A" & LR).FormulaR1C1 = ....some formula here

Problem begins when I have to create a range dynamically using (in my case) column header name. I can find the column by Header Name, get column number, convert it into letter, but any of the solutions I've tried didn't work.

This is what I use to get column number:

Function getColumn(searchText As String, wsname As String) As Integer
    Set aCell = Sheets(wsname).Rows(1).Find(what:=searchText, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If aCell Is Nothing Then
            MsgBox ("Column was not found. Check spelling")
            Exit Function
        Else
            getColumn = aCell.Column
        End If
End Function

This code I use to convert it into the letter:

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Now, to populate the formula in the range I tried something like that, but unfortunately no luck:

colLetter = Col_Letter(manuallyAdjustedNumber)
Range(colLetter & "2:" & colLetter & LR).FormulaR1C1 = "=EXACT([RC-1],[" & harnessDrawingNumber & " - " & manuallyAdjustedNumber & "])"

I will apreciate any help! Thank you!

Upvotes: 0

Views: 162

Answers (2)

Comintern
Comintern

Reputation: 22185

You are making this way too complicated. Why you are using R1C1 notation and at the same time converting everything back and forth from column letters to column numbers is completely beyond me.

You have 2 problems:

  • You are always working with the ActiveWorkbook (the example below makes that explicit).
  • You need to makes sure that the Worksheet is found.

It should look something more like this:

Public Function GetColumn(searchText As String, wsname As String) As Integer
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If LCase$(.Name) = LCase$(wsname) Then
                Dim header As Range
                Set header = .Rows(1).Find(searchText, , xlValues, xlWhole, xlNext, False)
                If Not header Is Nothing Then
                    GetColumn = header.Column
                    Exit Function
                End If
                MsgBox ("Column was not found. Check spelling")
                Exit Function
            End If
        End With
    Next
    MsgBox "Worksheet '" & wsname & "' not found."
End Function

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152450

Forget changing to a letter and just use Cells

 With Worksheets("Sheet1")
    .Range(.Cells(2,manuallyAdjustedNumber),.Cells(LR,manuallyAdjustedNumber)).FormulaR1C1 = ...
End With

Upvotes: 4

Related Questions