Undoing a bunch of direct cell references in my VBA code

T

TeePee04

New Member
Joined
Jan 30, 2019
Messages
1
Hey all,

I'm going to preface this message by stating that I'm a complete newbie with the more advanced features of Excel, inparticular VBA, so I’m well aware that the mistake I made was a spectacularlydumb one, and I apologise in advance for taking up your time with it -hopefullyit’s a relatively quick fix!
Long story short, I’ve been asked to build a report that canbe copy/pasted into an email, and that it needs to be in Excel and in aspecific format consisting of a number of smaller tables each with distinctdata. It doesn't make much sense to me to build it this way, but those are my orders! :)


The users of the form aren’t the most tech-savvy, so I needit to be as idiot-proof as possible. To that end, I created a button for eachtable that would add a new row to the relevant table and automatically merge therelevant cells so it fits the formatting of the table (yep, merged cells aswell!).

Through google and some trial and error, I managed to hacktogether a bit of code that seemed to do the job – at present it’s got threebuttons, one for each of the tables – I’ll include the code below:
Code: 
[LEFT][COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri][/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri]Private Sub CommandButton1_Click()[/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Rows("21:21").Insert Shift:=xlDown[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("B21", "C21").Merge[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("D21", "H21").Merge[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("B22").Copy[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("B21").PasteSpecial xlPasteFormats[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("D22").Copy[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("D21").PasteSpecial xlPasteFormats[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri]End Sub[/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri] [/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri]Private Sub CommandButton2_Click()[/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri]Rows("26:26").Insert Shift:=xlDown[/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("B26", "C26").Merge[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("D26", "H26").Merge[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("B27").Copy[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("B26").PasteSpecial xlPasteFormats[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("D27").Copy[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("D26").PasteSpecial xlPasteFormats[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri]End Sub[/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri] [/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri]Private Sub CommandButton3_Click()[/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri]Rows("32:32").Insert Shift:=xlDown[/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("B32", "C32").Merge[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("D32", "E32").Merge[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("F32", "G32").Merge[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]    Range("H32","I32").Merge[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("B33").Copy[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("B32").PasteSpecial xlPasteFormats[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("D33").Copy[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("D32").PasteSpecial xlPasteFormats[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("F33").Copy[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("F32").PasteSpecial xlPasteFormats[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("H33").Copy[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#222222][FONT=Calibri][COLOR=#000000][COLOR=#000000][FONT=Calibri]   Range("H32").PasteSpecial xlPasteFormats[/FONT][/COLOR][/COLOR][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][FONT=Calibri][SIZE=3][COLOR=#000000][COLOR=#000000][FONT=Calibri]End Sub[/FONT][/COLOR][/COLOR][/SIZE][/FONT][/FONT][/COLOR][/LEFT]
So, for those of you that haven’t already facepalmed hardenough to knock yourselves out, you can probably see where I’ve gone wrong here.The first button works just fine, but because I’ve used direct cell references,if I add anything to any of the earlier tables, it causes the later buttons tocopy the wrong row, breaking the whole thing.

I’m guessing I need to somehow get these cell references tobe relative to a certain point, so it takes into account any rows I’ve alreadyadded. I did think about adding an incremental counter to the sheet and justadding one to it every time you hit a button, but that didn’t pan out – if theythen manually delete a row, it won’t de-increment the counter, not to mentionthe fun and games of trying to work out how I’d avoid the reverse problem ofnot breaking the button for the first table if I added rows to the second.

Can any kind soul help me out of the mess I’ve dug formyself?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Hi. Take the first command button. You need to tell the code why it needs to use row 21. How did you make the decision to insert at row 21?
 
Upvote 0
I would set up a defined range of a neighbouring column to the tables that users är supposed to alter. Then tell the user to mark a row inside the defined range where they want a new One. Then the macro can use an if intersect statement to check against. That way they can only fire the macro in spots were the rows should be inserted becuase the macro only runs when they
have choosen a correct spot to insert rows.

So then you only need to use activecell.row as row reference to where the new row is going in.

The defined range Will expand if a row is inserted into it. Only Care needed to be taken is if users would need to insert at the first row below or on top of the range. To Combat this you can use hidden rows as anchors for the range (easy way) or you reset the range in VBA (much more compliated)


Another solution is to pop Up a userform were the user just type in a row number where they need a new row
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Modify VBA script to count rows with data in multiple sheets
Replies
0
Views
419
irishjsk
I
M
  • Question Question
Table issue with VBA
Replies
2
Views
405
Mike2
M
M
  • Question Question
Create button with update function for already present sheet
Replies
1
Views
673
SeniorNewbie
SeniorNewbie
E
  • Question Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
545
offthelip
O
N
  • Question Question
Count - specific consecutive order
Replies
6
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top