multiply each row in array by %

[john316swan]

I have a sheet with table of data and I need to apply a different percentage to each row of data going into the array. For example

AC	AD	AE	AF	...all the way to CR
Prorate	scholarship1	scholarship2	scholarship3	scholarship4
.67	5000	1000	2000	3000
.45	4000	3000	5500	2500
1	3500	1000	3500	1000
.98	1000	2000	3000	4000

Now i need to multiply each scholarship in the row (from column AD to column CR) by the corresponding % in column ac.

I can loop through but that takes a bit of time and i was trying to speed it up so i thought that an array would be best. Thanks for your help!

 

[KRice]

Do you care about overwriting your original scholarship amounts? If not, you can select all of the values in column AC, Ctrl-C to copy that selection array to the clipboard, and then select the entire group of scholarship values (i.e., all rows from column AD to column CR corresponding to the Prorate column), and perform a Paste > Paste Special and select the option to "Multiply" and confirm with OK.

That will overwrite your current scholarship values with the prorated amounts.

 

[john316swan]

Yes, that would work but i should have mentioned I am trying to write it in VBA.

 

[john316swan]

Ah, I figured it out from WiseOwl Tutorial:

Sub FormulaLoad()
Dim lr As Long
Dim MaxSch() As Variant
Dim dim1 As Long, dim2 As Long

Sheets(1).Activate
lr = Range("A1").End(xlDown).Row

'After scholarships have been prorated based on units, we're now going to prorate based on max sem eligibility
'Rows in Array, because these arrays begin at 0, we have to subtract 1 from each dimension
dim1 = lr - 1
'Columns in Array
dim2 = Range("AD2", Range("AD2").End(xlToRight)).Cells.Count - 1

'We declared it as a variant because we want a dynamic multi dimensional array so now that we now the rows and columns we can re-dimension the array
ReDim MaxSch(0 To dim1, 0 To dim2)

'Now we can write the values to the array
For dim1 = LBound(MaxSch, 1) To UBound(MaxSch, 1)
    For dim2 = LBound(MaxSch, 2) To UBound(MaxSch, 2)
        'here is where we input data from each cells
        MaxSch(dim1, dim2) = Application.WorksheetFunction.Round(Range("AD2").Offset(dim1, dim2).Value * Range("AB" & dim1 + 2), 0)
    Next dim2
Next dim1
'Delete it from memory
Erase MaxSch

End Sub

 

[KRice]

Excellent! Thanks for sharing.