VBA Looping (variable rows and variable columns)

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
46
I am working on writing a macro that will redistribute percent values so that the total equals 100%. The macro will loop through a various amount of rows and columns.

The starting row number is 21 and the macro will proceed to row 22 then 23 and so forth until there is no value in column B, This part I understand.

Dim x As Integer
x = 21
Do Until IsEmpty(Cells(x, 2)) 'Not sure if IsEmpty is fully correct since there is a formula in Column B that returns a blank value ("").

Enter formula here...

x = x +1

Loop

It is the column portion that is creating the confusion. The starting column is different each month (columns are based on weeks ending on a Sunday). The percentages mentioned above represent projected cash outflow. For example, in June I have percentages for July going forward. It is now July, I will now have percentages for August going forward. The previous part of the macro that is not mentioned here deletes the June percentages and hides the June columns so now the total does not equal 100% and only columns for future months are visible. I want to redistribute the remaining percentages (say 90%) so that they now reflect 100%. I have figured out a way to identify the starting column number and have denoted it as i, but if I keep adding 1 to i, won't that provide a bad starting number when I move down to the next row? Also, how do I loop through all the columns before moving down a row? I'm assuming there would be a loop within a loop. The columns would end where the header row doesn't contain a column header.

Any help would be greatly appreciated!
 

Attachments

  • Excel Looping.PNG
    Excel Looping.PNG
    48.9 KB · Views: 4

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,023
Office Version
  1. 2010
Platform
  1. Windows
This code shows you how to detect the last row with data and the last columns and then loop through them:
VBA Code:
lastrow = Cells(Rows.Count, "B").End(xlUp).Row ' this check for the last row with data in column B
lastcol = Cells(19, .Columns.Count).End(xlToLeft).Column ' This check for the last column with data in row 19

' to loop through each column and then go to the nextr row
  For i = 21 To lastrow  ' this starts on row 21
   For j = 4 To lastcol  ' this starts on column 4 which is column D
     ' put your formula here
   Next j
 Next i
 

Forum statistics

Threads
1,143,839
Messages
5,721,093
Members
422,339
Latest member
SHIVATVM

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
Top