SOLVED
Home

My macro turns my relative references to absolute.

%3CLINGO-SUB%20id%3D%22lingo-sub-716209%22%20slang%3D%22en-US%22%3EMy%20macro%20turns%20my%20relative%20references%20to%20absolute.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-716209%22%20slang%3D%22en-US%22%3E%3CP%3EI%20made%20a%20macro%20record%20and%20made%20sure%20to%20use%20relative%20references%2C%20I%20made%20rows%20to%20identify%20how%20may%20to%20insert%20via%20select%20then%20insert%20rows.%26nbsp%3B%20But%20it%20just%20became%20the%20dullest%20macro%20coz%20it%20refers%20to%20my%20references%20and%20makes%20it%20absolute%20the%20next%20time%20I%20would%20run%20it%2C%20it%20would%20use%20the%20last%20references%20as%20if%20it%20were%20absolute.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eeg.%26nbsp%3B%20I%20made%20a%20column%20that%20I%20would%20select%20a%20number%20of%20rows%20down%2C%20separated%20by%20a%20space%2C%20so%20these%20are%20block%20rows%20which%20I%20intended%20to%20be%20the%20basis%20of%20the%20number%20of%20rows%20I%20would%20insert.%20But%20the%20macro%20would%20deem%20them%20as%20absolute%20reference%2C%20so%20I%20tried%20editing%20the%20vba%20code.%26nbsp%3B%20I%20replaced%20the%20ranges%20to%20activecell.offset(%20the%20movements%20I%20made%20in%20selecting%20the%20block%20rows)-worked%20fine%20up%20until%20the%20selection%20of%20rows%20to%20insert.%26nbsp%3B%20I%20want%20it%20to%20be%20relative%20to%20the%20number%20of%20rows%20in%20each%20blocks%20I%20made.%26nbsp%3B%20Thank%20you%20so%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-716209%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-716286%22%20slang%3D%22en-US%22%3ERe%3A%20My%20macro%20turns%20my%20relative%20references%20to%20absolute.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-716286%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365432%22%20target%3D%22_blank%22%3E%40ihatebills%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20situation%20is%20not%20clear%20enough%20to%20me%20and%20there%20is%20no%20sample%20file.%3C%2FP%3E%3CP%3EHowever%2C%20In%20general%3C%2FP%3E%3CP%3E1-%20A%20relative%20macro%20runs%20RELATIVE%20to%20a%20Starting%20cell%3C%2FP%3E%3CP%3EBeside%20highlighting%20%22Use%20Relative%20Reference%22%20on%20the%20Developer%20Tab%2C%26nbsp%3Byou%20have%20to%20note%20which%20cell%20was%20selected%20when%20you%20started%20the%20recording.%3C%2FP%3E%3CP%3EWhen%20you%20run%20the%20Macro%2C%20it%20will%20repeat%20the%20recorded%20steps%20relative%20to%20the%20cell%20you%20select%20when%20running%20it.%3C%2FP%3E%3CP%3E2-%20Because%20the%20macro%20recorder%20doesn't%20necessarily%20create%20an%20efficient%20code%2C%20then%20try%20to%20shorten%20the%20code%20by%20using%20shortcuts%20while%20recording%2C%20like%20instead%20of%20using%20a%20drop%20down%20menu%20command%20to%20insert%20Rows%2C%20you%20can%20use%20the%20shortcut%20CTRL%20%2B%20(Plus%20sign%20on%20the%20NUMERIC%20keypad)%3CBR%20%2F%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719052%22%20slang%3D%22en-US%22%3ERe%3A%20My%20macro%20turns%20my%20relative%20references%20to%20absolute.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719052%22%20slang%3D%22en-US%22%3E%3CP%3EGot%20it%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E!%3C%2FP%3E%3CP%3EYou're%20the%20man!%26nbsp%3B%20Maybe%20its%20has%20been%20awhile%20for%20me%2C%20now%20some%20basic%20things%20are%20much%20clearer!%3C%2FP%3E%3CP%3EHave%20a%20great%20day!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719429%22%20slang%3D%22en-US%22%3ERe%3A%20My%20macro%20turns%20my%20relative%20references%20to%20absolute.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719429%22%20slang%3D%22en-US%22%3E%3CP%3ESub%20Macro1()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20Macro1%20Macro%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20Keyboard%20Shortcut%3A%20Ctrl%2Bq%3CBR%20%2F%3E'%3CBR%20%2F%3EActiveCell.Offset(0%2C%20-1).Range(%22A1%22).Select%3CBR%20%2F%3ESelection.End(xlUp).Select%3CBR%20%2F%3ESelection.End(xlUp).Select%3CBR%20%2F%3ESelection.End(xlUp).Select%3CBR%20%2F%3ESelection.End(xlDown).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%201).Range(%22A1%22).Select%3CBR%20%2F%3ERange(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3EActiveCell.Rows(%221%3A14%22).EntireRow.Select%3CBR%20%2F%3EActiveCell.Activate%3CBR%20%2F%3ESelection.Insert%20Shift%3A%3DxlDown%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eas%20per%20code%20above%20you%20can%20see%20activecell.rows(%221%3A14%22)%20is%20absolute%20I%20want%20it%20to%20be%3C%2FP%3E%3CP%3Ebased%20on%20the%20number%20of%20selected%20row%20my%20macro%20did.%3C%2FP%3E%3CP%3E%26nbsp%3BI%20put%20spaces%20in%20between%20rows%20to%20identify%20how%20many%20rows%20to%20select.%26nbsp%3B%20But%20in%20this%20macro%20it%20makes%20it%20absolute%20the%20next%20time%20I%20would%20run%20it%20even%20though%20it%20is%20just%203%20rows%20identified%20it%20will%20still%20insert%2014%20rows!%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
ihatebills
Occasional Contributor

I made a macro record and made sure to use relative references, I made rows to identify how may to insert via select then insert rows.  But it just became the dullest macro coz it refers to my references and makes it absolute the next time I would run it, it would use the last references as if it were absolute.

 

eg.  I made a column that I would select a number of rows down, separated by a space, so these are block rows which I intended to be the basis of the number of rows I would insert. But the macro would deem them as absolute reference, so I tried editing the vba code.  I replaced the ranges to activecell.offset( the movements I made in selecting the block rows)-worked fine up until the selection of rows to insert.  I want it to be relative to the number of rows in each blocks I made.  Thank you so much.

3 Replies
Solution

@ihatebills 

The situation is not clear enough to me and there is no sample file.

However, In general

1- A relative macro runs RELATIVE to a Starting cell

Beside highlighting "Use Relative Reference" on the Developer Tab, you have to note which cell was selected when you started the recording.

When you run the Macro, it will repeat the recorded steps relative to the cell you select when running it.

2- Because the macro recorder doesn't necessarily create an efficient code, then try to shorten the code by using shortcuts while recording, like instead of using a drop down menu command to insert Rows, you can use the shortcut CTRL + (Plus sign on the NUMERIC keypad)
Hope that helps

Nabil Mourad

 

Got it @nabilmourad!

You're the man!  Maybe its has been awhile for me, now some basic things are much clearer!

Have a great day!

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Rows("1:14").EntireRow.Select
ActiveCell.Activate
Selection.Insert Shift:=xlDown
End Sub

 

as per code above you can see activecell.rows("1:14") is absolute I want it to be

based on the number of selected row my macro did.

 I put spaces in between rows to identify how many rows to select.  But in this macro it makes it absolute the next time I would run it even though it is just 3 rows identified it will still insert 14 rows!??

Related Conversations