VBA Looping (variable rows and variable columns)

[mjohnston0209]

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!

 

[offthelip]

This code shows you how to detect the last row with data and the last columns and then loop through them:

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

 

[mjohnston0209]

Thank you! Just working on the formula for a variable range and then I am good to go!