Formula In Entire COlumn

[tejasdave]

Hello Guys,

I have a workbook with multiple worksheets.I have made a macro which does the cleanup of all the worksheets by performing below 3 steps:

1. Delete 1st Column in all sheets.
2. Delete all rows above cell containing "Header"
3. Add column "ID_Vs" in all sheets
4. Concatenate 2 values in the entire column.

I have the following code so far:

Sub LoopThroughSheets()
    Dim ws As Worksheet
    Dim lRowFound As Long
    Dim lColFound As Long
    Dim lColFound2 As Long
    Dim lngLastRow As Long
For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next 'Will continue if an error results
    ws.Range("A:A").Delete
    lRowFound = ws.UsedRange.Find("Header", LookIn:=xlValues).Row
    ws.Rows("1:" & lRowFound - 1).Delete Shift:=xlUp
    ws.Range("A:A").Insert
    ws.Range("A1") = "ID_Vs"
    lColFound = ws.UsedRange.Find("Header1", LookIn:=xlValues).Column
    lColFound2 = ws.UsedRange.Find("Header2", LookIn:=xlValues).Column
    ws.Cells(2, 1) = ws.Cells(2, lColFound) & ws.Cells(2, lColFound2)
Next ws
End Sub

The above code does everything but fails on one part.The problem is the code above only concatenates in cell A2 for each sheet and not the entire column.Filldown wont work as i have tried it.

Any help would be grateful.

Regards,
Tejas

[CatDaddy]

For i = 2 to lRowFound
    ws.Cells(i, 1) = ws.Cells(i, lColFound) & ws.Cells(i, lColFound2)
Next i

[Bob Phillips]

Loopless filldown

With ws.Cells(2, 1).Resize(lRowFound - 1)
    .FormulaR1C1 = "=RC" & lColFound & "&RC" & lColFound2
    .Value = .Value
    End With

[tejasdave]

@ CatDaddy: it is still the same.only concatenates in cell A2 and not entire column.

@ Xld: loopless filldown also does not seem to work.

Regards,

Tejas

[Bob Phillips]

I tested it on my data and in isolation, and it looked to work fine there.

Maybe post your workbook and we can see the actual problem.

[tejasdave]

I have uploaded the file for your reference.

Thanks ! !

[Bob Phillips]

None of your worksheets have the data that you look for in the headers, so what exactly are you trying to achieve?

[tejasdave]

ahhn...sorry forgot to change the column headers in the code.
Nevermind i got it solved:

Here is the code:

Sub LoopThroughSheets()
Dim ws As Worksheet
Dim lRowFound As Long
Dim lColFound As Long
Dim lColFound2 As Long
Dim lngLastRow As Long
'Dim i As Integer
For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next 'Will continue if an error results
    ws.Range("A:A").Delete
    lRowFound = ws.UsedRange.Find("Header1", LookIn:=xlValues).Row
    ws.Rows("1:" & lRowFound - 1).Delete Shift:=xlUp
    ws.Range("A:A").Insert
    ws.Range("A1") = "ID_Vs"
    ws.Range("A:A").NumberFormat = ("General")
    lColFound = ws.UsedRange.Find("Header1", LookIn:=xlValues).Column
    lColFound2 = ws.UsedRange.Find("Header2", LookIn:=xlValues).Column
    'For i = 2 To lColFound
        'ws.Cells(2, 1) = ws.Cells(2, lColFound) & ws.Cells(2, lColFound2)
        ws.Range("A2").Formula = "=OFFSET(A2,0," & lColFound - 1 & ") & OFFSET(A2,0," & lColFound2 - 1 & ")"
    'Next I
    ws.Range("A2").Copy
    ws.Paste ws.Range("A2:A" & ws.UsedRange.Rows.Count)
Next ws
End Sub

[CatDaddy]

you didnt use i in your loop...and you are using lColFound where it should be the last row

Try:

For i = 2 to Range("B" & Rows.Count).End(xlup).Row
    ws.Cells(i, 1) = ws.Cells(i, lColFound) & ws.Cells(i, lColFound2)
Next i

//assuming column b contains data to last row, if not choose appropriate column