[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Set range with Excel VBA

Posted on 2015-01-27
10
Medium Priority
?
452 Views
Last Modified: 2015-01-28
How to set whole column with Excel VBA, such that for example, column AB = column AA * column K

Tks
0
Comment
Question by:AXISHK
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40574616
If you want to set formula for whole column, try this.
ActiveSheet.Columns("AB").Formula = "=AA1*K1"

Open in new window

0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40574619
Hi,

pls try

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


Regards
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40574625
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40574667
Why would you do that? It's really inefficient to populate an entire column, especially with the new file formats.
0
 

Author Comment

by:AXISHK
ID: 40574686
.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
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40574702
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 400 total points
ID: 40574750
I imagine the error comes from trying to multiply the column headers together.
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40574755
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
 
LVL 5

Accepted Solution

by:
Hakan Yılmaz earned 1600 total points
ID: 40574757
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
 

Author Closing Comment

by:AXISHK
ID: 40575330
Tks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question