Excel VBA - Dynamic .Cell Formulas with Column letter variables

Question

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)"

It looks like you are missing an ampersand (aka &) as in "=Vlookup(" & eUTRANCol & 2 & ",SiteDatabase!I:W,15,0)". It also might be better in this case to fill the block of cells in the column all at once. — user4039065
– user4039065, Commented Mar 28, 2015 at 0:49
Thanks!! How would I fill the block of cells in the column all at once? Should I be using FormulaR1C1 or should I be using autofill? — Anurag Singh
– Anurag Singh, Commented Mar 28, 2015 at 3:22

user4039065 · Accepted Answer · 2015-03-28 03:57:17Z

0

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.

answered Mar 28, 2015 at 3:57

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

Anurag Singh Over a year ago

Thanks you this works perfectly and also avoids the use of function ConverttoLetter. The wb declaration was for the dictionary object which I create at the begining. Instead of setting each worksheet to for example set wsTrungReport = wb.sheets("TrungsReport") I loop through each worksheet and create wsNames so my workbook is more dynamic incase I add more worksheets. Take a look at this stackoverflow.com/questions/29176126/…

user4039065 Over a year ago

@AnuragSingh - This is actually another question and one that I will be happy to address once it is posted. The thread you've pointed to involves dictionary objects, but certainly not the way I would use them in your situation. If you start a new thread, please be as redacted SPECIFIC about your individual situation as you can.

Collectives™ on Stack Overflow

Excel VBA - Dynamic .Cell Formulas with Column letter variables

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related