- Oct 31, 2008
I have a large table that consists of 7 columns and 750 plus rows. Column B can be used to find the last row in the table. Now every cell has data in it. I have another cell that has a decimal, representing a percent value to increase each value in the table (5% cell reads 0.050). I have included some of my code below. How to I start at the top of one column, go all the way to the bottom, and then move tot he next column until all 7 columns are updated. Maybe I don't even need to worry about column position. I am open to slick efficient ways to get this done. I currently have a do until loop for one column, do I need to write the same loop for all 7 columns? Thanks for the help on this.
Dim priceUpdt As String Dim ChangePercent As String Dim LastRow As Long Dim sht As Worksheet Set sht = ActiveSheet LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row 'Asks user to confirm choice. i = MsgBox("Would you like to clear the pricing archive and update with current pricing?" & vbCrLf & "This cannot be undone!", vbYesNo + vbExclamation + vbDefaultButton2) If i = 7 Then 'NO Exit Sub ElseIf i = 6 Then 'YES Sheets("Pricing Table").Range("DA5:DG761").ClearContents Sheets("Pricing Table").Range("DA5:DG761").Value = Sheets("Pricing Table").Range("C5:I761").Value ' Cell X2 contains the decimal form of the percent increase dRate = Sheets("Pricing Table").Range("X2").Value priceUpdt = 1 + dRate ' Table Data starts in Cell C5, so C4 is where I need to start to get the loop to work Sheets("Pricing Table").Range("C4").Select Do Until ActiveCell.Row = LastRow ActiveCell.Offset(1, 0).Select If ActiveCell.Value = "" Then End If Else ActiveCell.Value = ActiveCell.Value * priceUpdt End If Loop End If