my first macro - super simple for any veteran... help appreciated!

[michael.tupper]

Since I dont have ANY experience programming (yet), I am limited to
creating macros by only recording them.

So, I recorded my first Macro, everything works great! But it only
works perfectly on the sheet I created it with and not on any other of
the same type, as intended. The issue is simple, the recorded macro
inserted a fixed range as shown here:

Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G289")
Range("G2:G289").Select

I simply must be able to replace the specified 'G289' in this case
with a variable associated to the row count in the Column G. I have
tried Help File, Googling for a while and just plain trying to learn
VBA programming on my own... but I have lost patience and am hoping
for a veteran to just tell me how to do it so I can get it done
finally!

Thanks in advance,
mtupper
VBA Rookie (future VBA junkie)

 

[Don Guillett]

sub fillitup()
lr=cells(rows.count,"g").end(xlup).row
Range("G2").AutoFill Destination:=Range("G2:G" & lr)
end sub

 

[FSt1]

hi
here is a sniple of code that many programmers use to find the last row.
Dim lr As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row

then in your code you could us lr like this
Range("G2").AutoFill Destination:=Range("G2:G" & lr)
Range("G2:G" &lr).Select

and unless you specified the sheet BY name, your code should run on any
active sheet.

Regards
FSt1

 

[SteveM]

Since I dont have ANY experience programming (yet), I am limited to
creating macros by only recording them.

So, I recorded my first Macro, everything works great! But it only
works perfectly on the sheet I created it with and not on any other of
the same type, as intended. The issue is simple, the recorded macro
inserted a fixed range as shown here:

Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G289")
Range("G2:G289").Select

I simply must be able to replace the specified 'G289' in this case
with a variable associated to the row count in the Column G. I have
tried Help File, Googling for a while and just plain trying to learn
VBA programming on my own... but I have lost patience and am hoping
for a veteran to just tell me how to do it so I can get it done
finally!

Thanks in advance,
mtupper
VBA Rookie (future VBA junkie)

Mike,

Do you mean that you have a contiguous but variable column of cells
starting at G2? If so, easy enough. Replace your selection code with
this Copy/Paste Equivalent:

Range("G2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

That's simply code from the macro recorder.

If the G column cells are unpopulated but you to enable the user to
specify to what G row he wants filled then you can have him place that
row number in a cell or capture it via an InputBox function.
Something like this:

Sub GetGRow()
Dim rowNum As Variant
rowNum = InputBox("Insert Last G Row Number")

 

[michael.tupper]

Awesome, Don, Thanks.

sub fillitup()
lr=cells(rows.count,"g").end(xlup).row
Range("G2").AutoFill Destination:=Range("G2:G" & lr)
end sub

 

[michael.tupper]

So copy.pasted the code, and I am getting -- Runtime error '1004':
Method 'Autofill' of object 'Range' failed.
Here's the code:

lr = Cells(Rows.Count, "g").End(xlUp).Row
Range("G2").AutoFill Destination:=Range("G2:G" & lr)
Range("G2:G" & lr).Select

Am I incorrect in assuming I can just insert this into the existing
Sub Macro() as ooposed to creating a new one just for this? I also
tried FSt1's version which looked pretty similar with the addition of
the "Dim lr As Long" and get the same... any ideas of what I am
doing wrong or leaving out?

Re-

 

[FSt1]

hi
if you are having trouble with the code "as written" then go back to your
original code ie
Range("G2").select
Selection.AutoFill Destination:=Range("G2:G" & lr)
and see if you get the same error.
usually you can avoid the select process by "connecting the dots" between
select and selection. speeds up the code a nana second or two. the code i
supplied worked on xl2003 except for the autofill. i didn't know what you
were autofilling so i didn't test that part but i saw nothing in the code i
supplied that would have caused the error. but if vb is keying on that line
then we have to trouble shoot that line.

post back if you have problems.

Regards
FSt1

 

[JLGWhiz]

Do you have an entry in Range("G2")? If it is blank, it will give that error
message.
If the data you want to autofill in in G1 then change the autofill line
accordingly.

 

[JLGWhiz]

One other thing to look for. Be sure the sheet you want the code to run for
is the active sheet, since you are not qualifying your range reference in the
snippet.

 

[michael.tupper]

To answer the multiple suggestions and inquiries:

First, G1 was a Column Title and G2 already had a formula in it and it
is in the only sheet in the workbook. So, thanks JLGWhiz, but that
wasn't it.

In the meantime I had found another thread about almost the same thing
and tried that and it worked using:
Range("G2:G" & cells(rows.count,1).End(xlup).row)

So, instead of using (rows.count, "G") I used (rows.count, 1) and that
worked. I assume 1 is referring to Column 1, so I tried column 7 (eq
to "G")... that didn't work either. And as I was writing this, I
realized why those col references weren't working: there was nothing
yet in column "G" as that is what I was wanting to fill with AutoFill,
but AutoFill references the row count of the col next to the column
your filling, so I tried (rows.count, "H") and viola. Then I
incorporated it back into the lr variable and it still works. I also
"connected the dots" and consolidated the Select phrases and collapsed
two lines of code into one and it still works fine.

FYI, I am on a MacBook Pro with Office 2004 for Mac.

Thanks everybody for your input.