# IF function VBA Excel

Hello,

I have three column - Column A, Column B, Column C

In Column A have different sort of data number

In Column B, I have a multiple number ranging from 1 - 10 till row 400.

In Column C, I have different type of data name.

My concern is where Column B = 1, 4, 5, copy the data of Column C into Column A

Can you do this in VBA coding. VBA must detect 1, 4, 5 til the last row. The number changes every week.

Thanks.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Sub FillCells()

Dim LastR As Long, r As Long

With ActiveSheet
LastR = .Cells(.Rows.Count, 2).End(xlUp).Row
For r = 2 To LastR  'use 1 instead of 2 if no header row
Select Case .Cells(r, 2)
Case 1, 4, 5: .Cells(r, 1) = .Cells(r, 3)
Case Else   'do nothing
End Select
Next
End With

MsgBox "Done"

End Sub
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
=IF(B1=1,C1,IF(B1=4,C1,IF(B1=5,C1,A1)))
0
Commented:
If you want a formula based solution, you can also use the OR() function to avoid having to nest your IFs:

=IF(OR(B1=1,B1=4,B1=5),C1,A1)
0
Commented:
Here is a quick macro which will do what you want...

``````Sub val()
dim rng as range
set rng=Range("C1:C"&CELLS(65536,"B").END(XLUP).ROW)
RNG.SELECT
SELECTION.FORMULA= "=IF(B1={1,4,5},A1,"""")"
SElection.copy
selection.pastespecial paste:=xlvalues
application.cutcopymode=false
range("a1").select
end sub
``````
0
Author Commented:
MatthewPatrick,
Your solution worked and it was excellent but need more to asked.
It worked in my example, i tried to put the formula in the report, it didn't do the job.
Column A (Example) = Column B (report)
Column B (Example) = Column C (report)
Column C (Example) = Column D (report)
I am starting on Row 8. Not the first row.

Sub FillCells()

Dim LastR As Long, r As Long

With ActiveSheet
LastR = .Cells(.Rows.Count, 2).End(xlUp).Row
For r = 2 To LastR  'use 1 instead of 2 if no header row
Select Case .Cells(r, 2)
Case 1, 4, 5: .Cells(r, 1) = .Cells(r, 3)
Case Else   'do nothing
End Select
Next
End With

MsgBox "Done"

End Sub
Is bold is known as column number? please clarify
0
Commented:
SherryRzv said:
>> I am starting on Row 8. Not the first row.

It would have been helpful for you to have stated that in your question.  Just change:

For r = 2 To LastR

to:

For r = 8 To LastR  'use 1 instead of 2 if no header row

and you should be good to go.
0
Author Commented:
We are getting there,
you know we did:
For r = 2 To LastR - it start on the second row. It worked only without any row or gap before the header
For r = 8 To LastR   - it didn't do the job i put them in the 8th row with row of gap before the header.. it sound strange.
if you could try add 7 row before the header row. You wll see the problem that program is not doing the job.
0
Commented:
SherryRzv,

It would be useful to see some sample data.  EE now allows you to directly upload

Please be advised that once you upload a file, it can be publicly accessed, and that it
may not be possible to fully and permanently delete it.  Therefore, be very careful about
posting proprietary, confidential, or other sensitive information.  If necessary, use "fake"
and/or obfuscated data in your sample.

Regards,

Patrick
0
Author Commented:
your formula was workign perfectly on other example. but not in the report. I will need to work out.
On the first 7 row, i have header title and number and that. It strange.
0
Commented:
As before, I am going to have to see sample data if you want me to figure out why it isn't working.
0
Author Commented:
Oh it working! i sorted it out! Thank Matthew!
I did some experiment. I figured it out!
I just wish i am pro at VBA programming.
0
Author Commented:
You have to understand the coding, it only for intermediate to understand it.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.