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?
 
Patrick MatthewsConnect With a Mentor 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
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.