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,

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Shikha" <(E-Mail Removed)> wrote in message
news:1E9A8AB1-E711-4139-9DC8-(E-Mail Removed)...
> 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,

Shikha,

Pl elaborate the expample with actual data.

H S Shastri



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

"Shikha" wrote:

> 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,

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" wrote:

> 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
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Shikha" <(E-Mail Removed)> wrote in message
> news:1E9A8AB1-E711-4139-9DC8-(E-Mail Removed)...
> > 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,
>
>

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
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Shikha" <(E-Mail Removed)> wrote in message
news:87F7C8A9-ED23-4B32-A713-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Shikha" <(E-Mail Removed)> wrote in message
>> news:1E9A8AB1-E711-4139-9DC8-(E-Mail Removed)...
>> > 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,
>>
>>

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:%23eGFyz$(E-Mail Removed)...
> 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
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Shikha" <(E-Mail Removed)> wrote in message
> news:87F7C8A9-ED23-4B32-A713-(E-Mail Removed)...
>> 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" wrote:
>>
>>> 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
>>>
>>> --
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> (E-Mail Removed)
>>> "Shikha" <(E-Mail Removed)> wrote in message
>>> news:1E9A8AB1-E711-4139-9DC8-(E-Mail Removed)...
>>> > 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,
>>>
>>>
>