Using variables in a VBA formula 1

[SaintAugustine]

I'm trying to write a macro that will run down the column to the right of a table and change the value of those empty cells to "value to left of active cell *divided by* table total" in order to let me see the percentages.

My loop seems to work fine. The problem I'm having is with getting VBA to perform a mathematical function using the variable I've defined as an integer.

In other words, my macro always croaks at:
ActiveCell.FormulaR1C1 = "=RC[-1]" / myTableTotal

Any help appreciated - thanks!

 

[AnotherHiggins]

Try this

[COLOR=blue white]ActiveCell = "=RC[-1]/" & myTableTotal[/color blue white]

The math is done in the worksheet, not in VB.

Hope that helps out,
John

 

[AnotherHiggins]

Actually, instead of a loop, try something like the following. It will run much faster than a loop on large tables. See notes below code

myTabletotal = Application.WorksheetFunction.Sum(Range("b:b")) [green]'set myTableTotal (You already seem to have this)[/green]
Range("c1") = "% of Total" [green]'insert heading for new column[/green]
Range("b2").Select [green]'select last column in data set[/green]
Selection.End(xlDown).Select [green]'select cell in last row containing data in data set[/green]
ActiveCell.Offset(, 1).Select [green]'go one cell to the right[/green]
Range(Selection, Selection.End(xlUp).Offset(1)).Select [green]'select range from activecell to top of section, not counting header[/green]
Selection = "=RC[-1]/" & Tabletotal [green]'enter this formula for each cell in the selection[/green]
Selection.NumberFormat = "0.00%" [green]'format selection as Percentage format[/green]

The code above makes the following assumptions:
[ul][li]B is the last column in the table, adjust all B's and C's in the code to suit your needs[/li]
[li]There are no null cells in the last column of the table - if there are null cells, post back[/li][/ul]

As I stated earlier, this should run a lot faster than looping through each row.

John

 

[SkipVought]

Hi,

Try to avoid SELECTING CELLS. It consumes unnecessary overhead and is usually an unprofessional coding solution.

Assuming that your table DATA starts with A1 AND the column of interest is B...

With [A1].CurrentRegion
  FirstRow = .Row
  LastRow = FirstRow + .Rows.Count - 1
End With
For r = FirstRow to LastRow
  With Cells(r, "B")
    .Value = .Offset(0, -1).Value / myTableTotal
  End With 
Next

Much simpler and straight forward.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

I'm thinking looping is entirely unnecessary here - utilise excel's inbuilt functionality:

With [A1].CurrentRegion
  FirstRow = .Row
  LastRow = FirstRow + .Rows.Count - 1
End With
[COLOR=green]'(Like your style for the above bit Skip !)[/color]
Range("B" & FirstRow).formula = "=A1/" & TableTotal
Range("B" & FirstRow).AutoFill Destination:=Range("B" & FirstRow & ":B" & LastRow)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff,

Why do I keep forgetting the Autofill method?

KISS! ==>*

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Skip - probably because it is a bit cumbersome in code. However, there is an even shorter way, utilising excel's ability to infer what you want to do:

'Set TableTotal variable
With [A1].CurrentRegion
  FirstRow = .Row
  lastrow = FirstRow + .Rows.Count - 1
End With
[b]Range("B" & FirstRow & ":B" & lastrow).Formula = "=A1/" & TableTotal[/b]

works because excel infers that you want to increment the A1 ref down the column

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question