PDA

View Full Version : [SOLVED:] Formula In Entire COlumn



tejasdave
05-22-2012, 09:26 AM
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
05-22-2012, 09:52 AM
For i = 2 to lRowFound
ws.Cells(i, 1) = ws.Cells(i, lColFound) & ws.Cells(i, lColFound2)
Next i

Bob Phillips
05-22-2012, 10:29 AM
Loopless filldown


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

tejasdave
05-23-2012, 12:12 AM
@ 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
05-23-2012, 01:07 AM
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
05-23-2012, 01:26 AM
I have uploaded the file for your reference.

Thanks ! !

Bob Phillips
05-23-2012, 02:28 AM
None of your worksheets have the data that you look for in the headers, so what exactly are you trying to achieve?

tejasdave
05-23-2012, 02:33 AM
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
05-23-2012, 11:27 AM
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