Solved

# IF function VBA Excel

Posted on 2008-11-17
560 Views
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.
0
Question by:SherryRzv

LVL 92

Accepted Solution

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

LVL 6

Expert Comment

=IF(B1=1,C1,IF(B1=4,C1,IF(B1=5,C1,A1)))
0

LVL 92

Expert Comment

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

LVL 59

Expert Comment

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 Comment

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

LVL 92

Expert Comment

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 Comment

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

LVL 92

Expert Comment

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 Comment

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

LVL 92

Expert Comment

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 Comment

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 Closing Comment

You have to understand the coding, it only for intermediate to understand it.
0

## Featured Post

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.