Formula Execution error!! Can someone correct the formula ?

[tv9_rohith]

Dim count As Long
Dim count2 As Long
Dim counta As Long
counta=1
count = Sheets("Sheet1").Range("A2").CurrentRegion.Rows.count
count2 = Sheets("Sheet2").Range("A2").CurrentRegion.Rows.count
Do Until counta = count
count2 = count2 + counta
Sheets("Sheet2").Range("A" & count2).Value = Sheets("Sheet1").Range("A" & counta).Value
counta = counta + 1
Loop
End Sub

The above mentoined code is written to mirror the same column values between 2 sheets. ( I.e., Sheet 1 -Column A values in Sheet 2 - Column A after clicking the command button ).


It can be done in a simpler way - Using formula instead of VBA.

=Sheet1!A2

coping it down as far as necessary until I have all the rows .

But only the thing why I need the VBA formula is

- I have some Mandatory checks in sheet 1, only if the particular cell value is True then the command button should be enabled and the Mirroring formula should execute and if the required cell value is false then Command button will be disabled so that the mirror doesnt proceed.

Hope some one can help me.

 

[Richard Schollar]

Perhaps:

Dim lngLastRow As Long

With Sheets("Sheet1")
  lngLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
End With

With Sheets("Sheet2").Range("A2:A" & lngLastRow)
  .FormulaR1C1 = "=Sheet1!RC1"
  .Value = .Value   'comment this line out if you want the original formulas
End With

 

[tv9_rohith]

I have to copy some more columns along with this .

How can I add that Columns in the formula.

For Example :

Nw we are mirroring Sheet1 to Sheet2 - Colum is A.

along with that I have to do mirror the below mentioned ones as well

Sheet 1 A column value to Sheet 2 A Column value.
Sheet 1 B Column value to Sheet 2 E Column Value.
Sheet 1 G Column value to Sheet 2 B Column value.
Sheet 1 B Column value to Sheet 2 C Column Value - Repeating B Column value - it should display in Sheet 2 Column C as well as E.

Thanks.

 

[Richard Schollar]

Try this:

Dim lngLastRow As Long

With Sheets("Sheet1")
  lngLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
End With

With Sheets("Sheet2").Range("A2:A" & lngLastRow)
  .FormulaR1C1 = "=Sheet1!RC1"
  .Offset(,1).FormulaR1C1 = "=Sheet1!RC7"
  .Offset(,2).FormulaR1C1 = "=Sheet1!RC2"
  .Offset(,4).FormulaR1C1 = "=Sheet1!RC2"
End With

 

[tv9_rohith]

I need small correction for the below :

For Example :

Nw we are mirroring Sheet1 to Sheet2 - Colum is A.

along with that I have to do mirror the below mentioned ones as well

Sheet 1 A column value( If only there is a value in Sheet 1 A Coulum then it should dispay the value in Sheet 2 A Column value or else it should be blank and should not display value a "0".
Sheet 1 B Column value to Sheet 2 E Column Value.
Sheet 1 G Column value to Sheet 2 B Column value.

Thanks.
<!-- / message -->

 

[Richard Schollar]

Hi

Try this:

Dim lngLastRow As Long

With Sheets("Sheet1")
  lngLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
End With

With Sheets("Sheet2").Range("A2:A" & lngLastRow)
  .FormulaR1C1 = "=IF(Sheet1!RC1<>"""",Sheet1!RC1,"""")"
  .Offset(,1).FormulaR1C1 = "=IF(RC[-1]<>"""",Sheet1!RC7,"""")"
  .Offset(,4).FormulaR1C1 = "=IF(RC[-4]<>"""",Sheet1!RC2,"""")"
End With