Set range with Excel VBA

How to set whole column with Excel VBA, such that for example, column AB = column AA * column K

Tks
AXISHKAsked:
Who is Participating?
 
Hakan YılmazTechnical Office MEP EngineerCommented:
or try this if you want to skip non numeric values.
Sub hakan()
    Dim iterrow As Range
    Application.Calculation = xlCalculationManual
    With ActiveSheet
        For Each iterrow In .UsedRange.Rows
            If IsNumeric(.Range("AA" & iterrow.Row).Value) And IsNumeric(.Range("K" & iterrow.Row).Value) Then
                .Range("AB" & iterrow.Row).Value = .Range("AA" & iterrow.Row).Value * .Range("K" & iterrow.Row).Value
            End If
        Next iterrow
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
If you want to set formula for whole column, try this.
ActiveSheet.Columns("AB").Formula = "=AA1*K1"

Open in new window

0
 
Rgonzo1971Commented:
Hi,

pls try

Range(Range("AB1"), Range("AB" & Range("K" & Rows.Count).End(xlUp).Row)).FormulaR1C1 = "=RC[-1]*RC[-17]"


Regards
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Hakan YılmazTechnical Office MEP EngineerCommented:
If you just want to put values instead formulas, please try this.
Sub hakan()
    Dim iterrow As Range
    Application.Calculation = xlCalculationManual
    With ActiveSheet
        For Each iterrow In .UsedRange.Rows
            .Range("AB" & iterrow.Row).Value = .Range("AA" & iterrow.Row).Value * .Range("K" & iterrow.Row).Value
        Next iterrow
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

0
 
Rory ArchibaldCommented:
Why would you do that? It's really inefficient to populate an entire column, especially with the new file formats.
0
 
AXISHKAuthor Commented:
.Range("AB" & iterrow.Row).Value = .Range("AA" & iterrow.Row).Value * .Range("K" & iterrow.Row).Value

It stops at above statement with "Type mistake"

The worksheet will create dynamically through VBA and I need to keep 1st row as heading.

Hakan's solution seems suitable in my case when it run with error, Any idea ?
0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
I am not getting any error, can you send a picture of code in your sheet?

@Rory You're right, it is bad to have whole column populated. Last code i've sent stops at the end of used range.
0
 
Rory ArchibaldCommented:
I imagine the error comes from trying to multiply the column headers together.
0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
please try this.
Sub hakan()
    Dim iterrow As Range
    Application.Calculation = xlCalculationManual
    With ActiveSheet
        For Each iterrow In .UsedRange.Rows
            .Range("AB" & iterrow.Row).Value = Val(Replace(.Range("AA" & iterrow.Row).Value, ",", ".")) * Val(Replace(.Range("K" & iterrow.Row).Value, ",", "."))
        Next iterrow
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

0
 
AXISHKAuthor Commented:
Tks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.