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.
SherryRzvAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Patrick MatthewsCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
JeewsCommented:
=IF(B1=1,C1,IF(B1=4,C1,IF(B1=5,C1,A1)))
0
Patrick MatthewsCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Saurabh Singh TeotiaCommented:
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

Open in new window

0
SherryRzvAuthor 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
Patrick MatthewsCommented:
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
SherryRzvAuthor 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
Patrick MatthewsCommented:
SherryRzv,

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

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
SherryRzvAuthor 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
Patrick MatthewsCommented:
As before, I am going to have to see sample data if you want me to figure out why it isn't working.
0
SherryRzvAuthor 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
SherryRzvAuthor 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.