VBA help... different formulas in specific Cells.

M

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hi,

Looking for some direction in terms of the best method for doing the following...

My data's like this:

<TABLE style="WIDTH: 100pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=133 border=0><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=67 height=20>Column A</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=66>Column B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Formula1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">blank</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Formula2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Formula1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">blank</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Formula2</TD></TR></TBODY></TABLE>

I need some code that will apply formula1 in the Cells in Column B only where the value in Column A is 1, and apply formula2 in Column B where the value in Column A is 3. For any other Cells, they should be left blank.

The data is these Columns will grow and shrink, so it needs to be dynamic too.

Thanks,

Matty
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Something like:

Code: 
Sub matty()
    Application.ScreenUpdating = False
    For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp)).Cells
        Select Case c
            Case 1
                c.Offset(, 1).Formula = "=rc[-1]*5"
            Case 2
                c.Offset(, 1).Formula = "=rc[-1]*15"
        End Select
    Next c
End Sub
I used select .. case rather than if as it makes it easier to add further value/formula combinations.

PS if the values are likely to change then you might need to clear column B every time you run it

Code: 
columns("B").clearcontents
before the for ... line

PPS does it absolutely have to be VB? You could use an if formula

=IF(A1=1,< INSERT FORMULA 1 HERE >,IF A1=2, < INSERT FORMULA 2 HERE >,""))

ought to do it.
 
Last edited:
Upvote 0
Matty said:
Hi,

Looking for some direction in terms of the best method for doing the following...

My data's like this:

<table style="width: 100pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="133"><colgroup><col style="width: 50pt;" width="67"><col style="width: 50pt;" width="66"></colgroup><tbody><tr style="height: 15pt;" height="20"><td class="xl63" style="border: 0.5pt solid windowtext; width: 50pt; height: 15pt; background-color: transparent;" height="20" width="67">Column A</td><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 50pt; background-color: transparent;" width="66">Column B</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 15pt; background-color: transparent;" align="right" height="20">1</td><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Formula1</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 15pt; background-color: transparent;" align="right" height="20">2</td><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">blank</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 15pt; background-color: transparent;" align="right" height="20">3</td><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Formula2</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 15pt; background-color: transparent;" align="right" height="20">1</td><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Formula1</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 15pt; background-color: transparent;" align="right" height="20">2</td><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">blank</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 15pt; background-color: transparent;" align="right" height="20">3</td><td class="xl63" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;">Formula2</td></tr></tbody></table>

I need some code that will apply formula1 in the Cells in Column B only where the value in Column A is 1, and apply formula2 in Column B where the value in Column A is 3. For any other Cells, they should be left blank.

The data is these Columns will grow and shrink, so it needs to be dynamic too.

Thanks,

Matty
Click to expand...

Hi!
here you are code
P.S: but it is possible with the formula (for instance choose() or if() or Value()...)

Sub MRExcel()
Dim cell As Range
For Each cell In Range("A3:A100")
If cell = 1 Then
cell.Offset(0, 1) = "Formula1"
ElseIf cell = 2 Then
cell.Offset(0, 1) = ""
ElseIf cell = 3 Then
cell.Offset(0, 1) = "Formula2"
End If
Next cell
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps

Forum statistics

Threads
1,224,527
Messages
6,179,345
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top