Applying formula to variable number of rows

[Shikha]

Hi All,
I have 2 worksheets, for which I am comparing data and showing result in 3rd
worksheet, in same workbook. I am able to do this for fixed number of rows by
dragging formula in 'result' sheet to no of rows present in Sheet1 and
Sheet2. However, number of rows in sheet1 and sheet2 is not fixed and I want
that formula in 'result' sheet automatically gets copied for no. of rows in
sheet1, by a click of a button. Is there any way to do this?

Regards,

 

[Don Guillett]

Sub formulainvariablerows()
lr = Sheets("sheet1").Cells(Rows.Count, "a").End(xlUp).Row
'MsgBox lr
Sheets("sheet2").Cells(2, "a").Resize(lr).Formula = "=a1*2"
End Sub

 

[HARSHAWARDHAN. S .SHASTRI]

Shikha,

Pl elaborate the expample with actual data.

H S Shastri



+++++++++++++++++++++++++++++++++++++++++++++++++++

 

[Shikha]

Thanks Don, it worked well with a single column data. Now, building on this
further, my data is present row wise from A11, A22 and so on. Its working
well on A1, A2 but does not percolate row wise. How to do that?
Second query: I also want the conditional formatting that I have applied on
first row to be applied to variable number of rows below it. Any suggestions?

 

[Don Guillett]

The macro does copy the formula into sheet2 from row 2 to the last row on
sheet 1.
If desired, send your wb to my address with a snippet of this and complete
details and before/after examples.

 

[Don Guillett]

Sub formulainvariablerows()
lr = Sheets("Sheet1").Cells(Rows.Count, "a").End(xlUp).Row
'lc = Sheets("Sheet1").Cells(1, Columns.Count).End(xlUp).Column
'I changed to
lc = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

Sheets("result").Cells(2, "a").Resize(lr, lc).Formula = _
"=COUNTIF(Sheet1!A1,Sheet2!A1:AG1)>0"

'I added
With Sheets("Result").Range(Cells(2, 1), Cells(lr + 1, lc))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlEqual, Formula1:="FALSE"
.FormatConditions(1).Interior.ColorIndex = 46
End With
End Sub