Can I use IF to put data in cells?

optionman

New Member
Joined
May 23, 2004
Messages
7
Hi Excel experts,

This is what I want to do: if cell b3=SAME, then fill cells d3:d52 with the contents of b4 (which would just be a number). If b3 does not equal SAME, then I want to enter numbers into d3:d5 manually on the keyboard. I thought I could do this with an IF statement and have tried several different formulas, but I can't get it to work. Obviously I am sort of a novice and would really appreciate any information on how to accomplish this task. Thank you very much.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi optionman
Welcome to the board

A formula is just a way of specifying the value of the cell. This means that you cannot use a formula in a cell to change anything in another cell.

In your case you can use the Change Event of the worksheet. Whenever B3 receives the value "SAME", you write the contents of b4 in d3:d52.

Paste this code in the worksheet's module (right-click on the worksheet's tab and choose view code):

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
    If UCase(Target.Value) = "SAME" Then
        Range("D3:D52").Value = Range("B4").Value
    End If
End If
End Sub

To test it write SAME in b3. You'll see the value of b4 copied to d3:d52. If you write anything else in b3, nothing happens.

Hope this helps
PGC
 

optionman

New Member
Joined
May 23, 2004
Messages
7
This code works perfectly! You have know idea how much time and work this will save me. I now see that it will be worth my time to learn (at least the basics) this program language. Is there any particular book or CD, etc. that you could recommend? Thank you so much.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
I'm glad things work the way you needed.

If you want to learn vba I can tell you 2 things that helped me a lot.

The macro recorder was one. Although the macro recorder is very bad in terms of programming structure, it helps a lot in the beginning to identify which objects, properties and methods you can use to perform an action.

The other thing that helped me a lot was to look at the solutions posted here in this board. There are lots of posts in all excel's areas of expertise and lot of experienced members posting.

Kind regards
PGC
 

Forum statistics

Threads
1,136,712
Messages
5,677,335
Members
419,688
Latest member
sarahmichelle

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
Top