CODEWITHSUNDEEP
excelvba
Anurag Singh
Anurag Singh

Reputation: 120

Excel VBA - Dynamic .Cell Formulas with Column letter variables

I am putting formulas in a worksheet which is dynamic meaning data for row and column keeps changing and also the position for column headers also keeps changing. I need to find the last row and last col and place 4 columns at the end and put formulas autofilled to last row. I have the header names which I find in the row first and then convert column number to letter. Now I need to place formulas in column based on these new column letters. I have the following code so far. The Vlookup formula is not working.

Dim eNB as Integer
Dim eNBCol as string
Dim eUTRAN as integer
Dim eUTRANCol as string

With Rows(1)
   Set d = .Find("ENODEB")
    If Not d Is Nothing Then
     eNB = d.Column
    End If
Set d = .Find("EUTRANCELL")
    If Not d Is Nothing Then
     eUTRAN = d.Column
    End If
End With

eNBCol = ConvertToLetter(eNB)
eUTRANCol = ConvertToLetter(eUTRAN)

wb("wsTrungReport").Cells(1, lstCol + 1).Value = "Cell-ID"
wb("wsTrungReport").Cells(2, lstCol + 1).Formula = "=" & eNB_Col & 2 & "&" & eUTRANCol & 2
wb("wsTrungReport").Cells(1, lstCol + 2).Value = "Manager"
'Vlookup formulas is not working
wb("wsTrungReport").Cells(2, lstCol + 2).Formula = "=Vlookup(" & eUTRANCol & 2 ",SiteDatabase!I:W,15,0)"

Upvotes: 0

Views: 1699

Answers (1)

user4039065
user4039065

Reputation:

You are testing to see if both ENODEB and EUTRANCELL are found but in reality, you are continuing to process even if they are not found. Better to include the further processing when they have both been located and error out if not.

There is no loop in your code to run through the cells in the columns you are adding to the end but there doesn't need to be if you grab the last populated cell from either the ENODEB or EUTRANCELL column (since they seem to be the most relevant) and resize the formula assignment to a range extending down to that last populated row.

I prefer a more descriptive syntax for something like wb("wsTrungReport").Cells(...) but you can modify what I've supplied if you syntax works for you. I'm totally on board with abandoning the xlA1 references and the custom ConvertToLetter functions in favor of xlR1C1 reference style.

Dim eNB As Long, eUTRAN As Long, lr As Long, lc As Long, wb As Workbook

Set wb = ThisWorkbook

With wb.Sheets("wsTrungReport")  'don't know where wb comes from
    If CBool(Application.CountIf(.Rows(1), "ENODEB")) And _
       CBool(Application.CountIf(.Rows(1), "EUTRANCELL")) Then

        'find the numerical column indexes to reference
        eNB = Application.Match("ENODEB", .Rows(1), 0)
        eUTRAN = Application.Match("EUTRANCELL", .Rows(1), 0)

        'find the extents of the data
        lr = .Cells(Rows.Count, eUTRAN).End(xlUp).row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column

        .Cells(1, lc + 1) = "Cell-ID"
        .Cells(2, lc + 1).Resize(lr - 1, 1).FormulaR1C1 = "=RC" & eNB & "&RC" & eUTRAN
        .Cells(1, lc + 2) = "Manager"
        .Cells(2, lc + 2).Resize(lr - 1, 1).FormulaR1C1 = "=vlookup(RC" & eUTRAN & ", SiteDatabase!C9:C23, 15, 0)"

    Else
        debug.print "Not found!"
    End If
End With

Set wb = Nothing

This seems to be a part of something larger so you may want to remove the wb declaration and both of the assignments.

This code compiles but due to the workbook and worksheet name assignments, has not been fully tested.

Upvotes: 0

Related Questions