CODEWITHSUNDEEP
vbaexcel
user8384641
user8384641

Reputation: 39

VBA - how do I divide a range of cells by a fixed cell

I have a spreadsheet with a column of values that I would like to divide by a fixed cell (say C3), and have the results in an adjacent column.

I would like this code to run to the last available row (with values) as well.

Would greatly appreciate any help! Thanks!

Upvotes: 0

Views: 1760

Answers (2)

YowE3K
YowE3K

Reputation: 23984

If your source values were in, for instance, A1:A7 and you want to copy them to B1:B7 and divide by C3 at the same time, you could:

With ActiveSheet
    'Determine last row
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    'Copy the original values from column A to column B
    .Range("B1:B" & lastRow).Value = .Range("A1:A" & lastRow).Value

    'Copy / Pastespecial Divide using cell C3
    .Range("C3").Copy
    .Range("B1:B" & lastRow).PasteSpecial Paste:=xlPasteValues, _
                                          Operation:=xlDivide, _
                                          SkipBlanks:=False, _
                                          Transpose:=False
    Application.CutCopyMode = False                                              
End With

Upvotes: 1

kevin
kevin

Reputation: 183

You can use Do While

Do While Cells(iCol, 3).Value <> ""
    'Do some thing
    iCol = iCol + 1
Loop

Upvotes: 0

Related Questions