Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Set range with Excel VBA

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

Tks
0
AXISHK
Asked:
AXISHK
  • 5
  • 2
  • 2
  • +1
2 Solutions
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
AXISHKAuthor Commented:
Tks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now