| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED VB TO FILL A ROW WITH ABSOLUTE CELL REFERENCE
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
NEED VB TO FILL A ROW WITH ABSOLUTE CELL REFERENCE
On Jul 16, 4:13 pm, CAPTGNVR wrote:
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 $7="","",PAWY_INPUT!$G$7) and cell A3 to =IF(PAWY_INPUT!$G $8="","",PAWY_INPUT!$G$8) and so on till 50th row. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
NEED VB TO FILL A ROW WITH ABSOLUTE CELL REFERENCE
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" wrote: On Jul 16, 4:13 pm, CAPTGNVR wrote: 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 $7="","",PAWY_INPUT!$G$7) and cell A3 to =IF(PAWY_INPUT!$G $8="","",PAWY_INPUT!$G$8) and so on till 50th row. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
NEED VB TO FILL A ROW WITH ABSOLUTE CELL REFERENCE
On Jul 16, 5:10 pm, Mike H wrote:
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" wrote: On Jul 16, 4:13 pm, CAPTGNVR wrote: 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 $7="","",PAWY_INPUT!$G$7) and cell A3 to =IF(PAWY_INPUT!$G $8="","",PAWY_INPUT!$G$8) and so on till 50th row. 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
NEED VB TO FILL A ROW WITH ABSOLUTE CELL REFERENCE
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 -- Gary "CAPTGNVR" wrote in message ps.com... On Jul 16, 4:13 pm, CAPTGNVR wrote: 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 $7="","",PAWY_INPUT!$G$7) and cell A3 to =IF(PAWY_INPUT!$G $8="","",PAWY_INPUT!$G$8) and so on till 50th row. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
NEED VB TO FILL A ROW WITH ABSOLUTE CELL REFERENCE
On Jul 16, 8:49 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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 -- Gary "CAPTGNVR" wrote in message ps.com... On Jul 16, 4:13 pm, CAPTGNVR wrote: 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 $7="","",PAWY_INPUT!$G$7) and cell A3 to =IF(PAWY_INPUT!$G $8="","",PAWY_INPUT!$G$8) and so on till 50th row. 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
NEED VB TO FILL A ROW WITH ABSOLUTE CELL REFERENCE
you need to put the following all on 1 line
..Formula = "=if(PAWY_INPUT!$G" & .Row + 5 & "="""","""",PAWY_INPUT!$G" & .Row() + 5 & ")" -- Gary "CAPTGNVR" wrote in message ups.com... On Jul 16, 8:49 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote: 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 -- Gary "CAPTGNVR" wrote in message ps.com... On Jul 16, 4:13 pm, CAPTGNVR wrote: 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 $7="","",PAWY_INPUT!$G$7) and cell A3 to =IF(PAWY_INPUT!$G $8="","",PAWY_INPUT!$G$8) and so on till 50th row. 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
NEED VB TO FILL A ROW WITH ABSOLUTE CELL REFERENCE
On Jul 16, 11:42 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
you need to put the following all on 1 line .Formula = "=if(PAWY_INPUT!$G" & .Row + 5 & "="""","""",PAWY_INPUT!$G" & .Row() + 5 & ")" -- Gary "CAPTGNVR" wrote in message ups.com... On Jul 16, 8:49 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote: 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 -- Gary "CAPTGNVR" wrote in message oups.com... On Jul 16, 4:13 pm, CAPTGNVR wrote: 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 $7="","",PAWY_INPUT!$G$7) and cell A3 to =IF(PAWY_INPUT!$G $8="","",PAWY_INPUT!$G$8) and so on till 50th row. 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 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
NEED VB TO FILL A ROW WITH ABSOLUTE CELL REFERENCE
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 -- Gary "CAPTGNVR" wrote in message ups.com... On Jul 16, 11:42 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote: you need to put the following all on 1 line .Formula = "=if(PAWY_INPUT!$G" & .Row + 5 & "="""","""",PAWY_INPUT!$G" & .Row() + 5 & ")" -- Gary "CAPTGNVR" wrote in message ups.com... On Jul 16, 8:49 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote: 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 -- Gary "CAPTGNVR" wrote in message oups.com... On Jul 16, 4:13 pm, CAPTGNVR wrote: 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 $7="","",PAWY_INPUT!$G$7) and cell A3 to =IF(PAWY_INPUT!$G $8="","",PAWY_INPUT!$G$8) and so on till 50th row. 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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| absolute reference fill | Excel Worksheet Functions | |||
| Absolute cell reference will not remain absolute. | Excel Worksheet Functions | |||
| Absolute Cell Reference | Excel Discussion (Misc queries) | |||
| How can I do an Absolute Cell Reference but non Absolute Cell Pais | Excel Programming | |||
| How can I do an Absolute Cell Reference but non Absolute Cell Pais | Excel Programming | |||
Linear Mode
