1st Excel Macro -- Reference to a specific cell not wanted

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FamilyGuy902 - 16 Nov 2006 20:32 GMT

I have recorded an excel Macro. I am trying to paste a function down
for as many rows that are in my file. However, as shown in the
following code, it is making reference to cell C2926, which happens to
be the last row in the file that I used to record the macro. It causes
my macro to crash if there are a different amount of rows in my file.

Selection.End(xlDown).Select
Range("C2926").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste

Does anyone know what the proper code should be, or what sequence of
commands I should do when recording the macro? I thought by doing
end->down arrow would be the way to go, but I guess not....

Thanks,
Jason

Reply to this Message

Don Guillett - 16 Nov 2006 20:52 GMT

You didn't post it all but you want to remove selections whereever possible

lr = Cells(Rows.Count, "c").End(xlUp).Row
With Range("c2:c" & lr)
.FillDown
.Value = .Value 'change formula to value NO overhead
End With

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I have recorded an excel Macro. I am trying to paste a function down
> for as many rows that are in my file. However, as shown in the
[quoted text clipped - 13 lines]
> Thanks,
> Jason

Reply to this Message

Gord Dibben - 16 Nov 2006 21:17 GMT

If you are sure there are no blanks in column C...........

Sub selectrange2()
Range(Range("C1"), Range("C1").End(xlDown)).Copy _
Destination:=Sheets("Sheet2").Range("A1")
End Sub

If there may be blanks work from the selected cell to the bottom of sheet then
up to data.............

Sub selectrange3()
Range("C1").Select
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Copy _
Destination:=Sheets("Sheet2").Range("A1")
End Sub

Gord Dibben MS Excel MVP

>I have recorded an excel Macro. I am trying to paste a function down
>for as many rows that are in my file. However, as shown in the
[quoted text clipped - 13 lines]
>Thanks,
>Jason

Reply to this Message

FamilyGuy902 - 17 Nov 2006 15:48 GMT

Thanks for the responses. I've tried to incorporate your code into
mine, but I keep getting errors. I don't know visual basic, so I'm not
good at editing it. I've re-recorded the macro, and I'm including the
entire code here. Can someone please take a stab at editing my code.
Thanks!!!

Sub NewMacro()

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("C2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],"","",RC[-1])"
Selection.Copy
Range("B2").Select
Selection.End(xlDown).Select
Range("C2926").Select ' ***HERE IS THE PROBLEM***
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("C1").Select
ActiveCell.FormulaR1C1 = "New Name"
End Sub

> If you are sure there are no blanks in column C...........
>
[quoted text clipped - 31 lines]
> >Thanks,
> >Jason

Reply to this Message

FamilyGuy902 - 23 Nov 2006 13:59 GMT

Hello. Could someone who understands Visual Basic take a stab at
helping me with this. Thanks in advance.

> Thanks for the responses. I've tried to incorporate your code into
> mine, but I keep getting errors. I don't know visual basic, so I'm not
[quoted text clipped - 69 lines]
> > >Thanks,
> > >Jason

Reply to this Message