VBA function to shift columns by variable value starting with variable column number

kt_mr_excel

New Member
Joined
Sep 24, 2021
Messages
15
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Help! I have a very large volume of spreadsheets that were converted from PDF files and need to correct some column shifts that resulted from the conversion. The worksheets have thousands of rows that would need all cells to be shifted to the right from a variable starting column and variable amount by row. I have two columns, A & B, where A displays the letter value of the starting column to shift (like "L") and B contains a numeric value indicating the amount to shift. So ideally, I need to start on row 2 (to avoid the header row) and loop through all rows that contain data (rather than a fixed number of rows) and perform the appropriate shift for each row based on the values in columns A & B. I've found some similar logic in the forum but nothing that handles all of the variables. Any help will be be GREATLY appreciated!
 
I believe that all of the results are correct.
Okay. (Relieved.)

I am currently using a spreadsheet that calculates and performs the offsets (with a much less elegant approach) but it's beyond my comprehension how to accomplish that with VBA and loop through multiple workbooks within the folder.
As long as all of the data for one record set is contained on each spreadsheet, it's all good!

I assume that there needs to be someplace in the code to store the two criteria values (text string to search for and adjustment value to locate the offset starting point)?
There are a number of ways to do it. What's the format of the spreadsheets that don't have columns A and B (my previous sub's input)? Does the data start on row 2 and in column 1? If so, we can easily insert columns to the left of the data with VBA and put these values there. And I can easily modify my previous row shift subroutine to take in the column numbers rather than letters. (But we don't have to insert these "instructions into columns A and B in the spreadsheets at all. I can pass them directly to my previous sub.)

So the basic idea is for me to write a sub to create all of the formulas that you see in that sheet and insert them into your data sheet. From there, I will have another sub to insert those into the data sheets (or, again, I can skip this step). Then I run my old sub. Then I delete the formulas inserted in with the first sub.

And then I do all of that for every sheet in every workbook in a folder.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
And another question/comment.

Obviously the input (besides the Excel Workbooks) has now evolved into a two-column table of the text strings and adjustment values. So please specify where those will be in the Workbooks.

For example, if you don't have them in the Workbooks already, you can put them in columns A and B in the sheet with the data. (Since each sheet has its own corresponding adjustment values and text strings.) In each spreadsheet, select the column A header so that the entire column A becomes selected (light gray shade), right click, and select insert. (Do this twice so that there are two new columns to under A and B column headers). Then put the text strings in column A starting on row 1 and the adjustment values in column B starting on row 1.

(But of course save a copy of the Workbooks before we do anything!)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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