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