NEED VB TO FILL A ROW WITH ABSOLUTE CELL REFERENCE

[CAPTGNVR]

DEAR ALL
I want to place this formula l in say A-1 =IF(PAWY_INPUT!$G
$6="","",PAWY_INPUT!$G$6).
By using VB I want to fill this to next 50 rows - that is A1.A50.
Pls help.

 

[CAPTGNVR]

DEAR ALL
I want to place this formula l in say A-1 =IF(PAWY_INPUT!$G
$6="","",PAWY_INPUT!$G$6).
By using VB I want to fill this to next 50 rows - that is A1.A50.
Pls help.

Further Requirement: When the VB is run cell A2 to have
=IF(PAWY_INPUT!$G

 

[Guest]

Hi,

It would be far simpler to drag your formula manually but if you must have
VBA then try this:-

Sub stantiate()
Worksheets("Sheet1").Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(PAWY_INPUT!R[5]C7="""","""",PAWY_INPUT!R[5]C7)"
Selection.AutoFill Destination:=Range("A1:A50"), Type:=xlFillDefault
Range("A1").Select
End Sub

Mike

 

[CAPTGNVR]

Hi,

It would be far simpler to drag your formula manually but if you must have
VBA then try this:-

Sub stantiate()
Worksheets("Sheet1").Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(PAWY_INPUT!R[5]C7="""","""",PAWY_INPUT!R[5]C7)"
Selection.AutoFill Destination:=Range("A1:A50"), Type:=xlFillDefault
Range("A1").Select
End Sub

Mike

Further Requirement: When the VB is run cell A2 to have
=IF(PAWY_INPUT!$G

D/MIKE
Thnks ur sugestion. Pls see my code below and sugest for line 20 how
to make it fill so that each cell increases by the next cell row
number like $g$8, $g$9, $g$10 and so on.



Sub stantiate()
'Worksheets("54B").Range("AB15").Select
Selection.Copy
Worksheets("54B").Range("B15").Select
ActiveSheet.Paste

'ActiveCell.FormulaR1C1 = "=IF(PAWY_INPUT!R[5]C7="""","""",PAWY_INPUT!
R[5]C7)"
20 Selection.AutoFill Destination:=Range("B15:B30"),
Type:=xlFillDefault
Range("A1").Select
End Sub

Actual need is I have say A1.N1 which has in some cells absolute
reference, some relative and some cells with formulas.
So i want to use range A1.N1.select and copy it to next 50 rows, so
that the copied cells has the same as A1.N1. Like if it is absolute $g
$8 the cell below to have it as $g$9; in the next column if it is
M5+N5 then cell below should be M6+N6.
Pls sugest. I wl keep checking for ur response.
rgds/captgnvr

 

[Gary Keramidas]

you can give this a try, the formula line is all one line, i tried to split it
in case of word wrap

Sub test()
With Range("A1").Resize(50)
..Formula = "=if(PAWY_INPUT!$G" & .Row + 5 & "="""","""",PAWY_INPUT!$G" & .Row()
_
+ 5 & ")"
End With
End Sub

 

[CAPTGNVR]

you can give this a try, the formula line is all one line, i tried to split it
in case of word wrap

Sub test()
With Range("A1").Resize(50)
.Formula = "=if(PAWY_INPUT!$G" & .Row + 5 & "="""","""",PAWY_INPUT!$G" & .Row()
_
+ 5 & ")"
End With
End Sub

D/GARY
With Range("A1").Resize(50).Formula = "=if(PAWY_INPUT!$G" & .Row + 5 &
"="""","""",PAWY_INPUT!$G" & .Row() + 5 & ")"
when i use this i get "COMPILE ERROR --- INVALID OR UNQUALIFIED
REFERENCE" AT ".ROW"


Actual need is I have say A1.N1 which has in some cells absolute
reference (PAWY_INPUT!$G$5), some relative (M5) and some cells with
formulas (M5+PAWY_INPUT!$G$5).
So i want to use range A1.N1.select and copy it to next 50 rows, so
that the copied cells has the same as A1.N1. Like if it is absolute
(PAWY_INPUT!$G$5 the cell below to have it as (PAWY_INPUT!$G$6); in
the next column if it is
M5+N5 then cell below should be M6+N6.
Pls sugest. I wl keep checking for ur response.
rgds/captgnvr

 

[Gary Keramidas]

you need to put the following all on 1 line

..Formula = "=if(PAWY_INPUT!$G" & .Row + 5 & "="""","""",PAWY_INPUT!$G" & .Row()
+ 5 & ")"

 

[CAPTGNVR]

you need to put the following all on 1 line

.Formula = "=if(PAWY_INPUT!$G" & .Row + 5 & "="""","""",PAWY_INPUT!$G" & .Row()
+ 5 & ")"

D/GARY
THNKS a lot . It worked for one cell. LEARNT A NICE ONE FOR TODAY.

Now may I request you for next grade.
How to copy A5.N5 and paste to next 50 rows.

Row A5.N5 has in some of the cells absolute reference (PAWY_INPUT!$G
$5), someof the cells with relative (M5) and some of the cells with
formulas (M5+PAWY_INPUT!$G$5).

So i want to use range A1.N1.select and copy it to next 50 rows, so
that the copied cells has like if it is absolute (PAWY_INPUT!$G$5 the
cell below to have it as (PAWY_INPUT!$G$6); in
the next column if it is M5+N5 then cell below should be M6+N6.

I will select the range("a1.n1").select
selection.copy
what is the code to copy it to the next 50 rows which should have the
absolute and relatives copied as per the cell above.
rgds/captgnvr

 

[Gary Keramidas]

i'm not completely sure what you're trying to do. to me, in the examples, the
rows are overlapping.

but try this to do your a1:n1 copy


With Range("A1:N1")
..Copy
..Offset(1).Resize(50).PasteSpecial
End With