Transferring VBA code to another cell

Joek88

New Member
Joined
Aug 17, 2023
Messages
37
Office Version
  1. 2021
Platform
  1. Windows
Ok, I have wrote a section of code that is fairly long and includes many different cells. My question is this: Can I use the VBA code that I have written and push that code to another cell? I do not just want to simply copy and paste the logic into a cell, but I want the logic to be changed to look a the cells and reformat all the cells to match the new location.

So here is part of my VBA code below. It basically consist of cell D21 with a dropdown selection of 1,2, or 3. It merges and changes cells based on the selection made in the dropdown. However, I want to move the "logic" part of it to D28. Doing this the "old fashion" way, I would have to go through the entire code and change each cell to it's apporiate new location. This is EXTREMELY time consuming and leads to alot of error. There HAS to be an easier way to achieve this. D28 also has the exact dropdown as D21. I want the code to change based on the new location. So this is why a simple copy and paste will not work. Can this be done or am I dreaming? I can provide my entire code if necessary.

Private Sub HandleCase1()

' Merges and unmerges cells based on Case1 being selected

Range("C21:C26").merge
Range("C21").Value = "-"
Range("C21:C26").Borders(xlEdgeBottom).linestyle = xlContinuous
Range("C28:C33").merge
Range("C28").Value = "-"
Range("C28:C33").Borders(xlEdgeTop).linestyle = xlContinuous
Range("C28:C33").Borders(xlEdgeBottom).linestyle = xlContinuous
Range("C35:C40").merge
Range("C35").Value = "-"
Range("C35:C40").Borders(xlEdgeTop).linestyle = xlContinuous
Range("D21:D33").UnMerge
Range("D21:D26").merge
Range("D27").Interior.ColorIndex = xlNone
Range("D21:D26").Borders(xlEdgeBottom).linestyle = xlContinuous
Range("D28:D33").Borders(xlEdgeTop).linestyle = xlContinuous
Range("D28:D33").merge
If Range("D28").Value = "" Then Range("D28").Value = 1
Range("D34").Interior.ColorIndex = xlNone
Range("D35:D40").merge
Range("D28:D33").Borders(xlEdgeBottom).linestyle = xlContinuous
Range("D28:D33").Borders(xlEdgeTop).linestyle = xlContinuous
Range("D35:D40").Borders(xlEdgeTop).linestyle = xlContinuous
If Range("D35").Value = "" Then Range("D35").Value = 1
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,224,259
Messages
6,177,480
Members
452,782
Latest member
ZCapitao

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