?
Solved

IF function VBA Excel

Posted on 2008-11-17
12
Medium Priority
?
563 Views
Last Modified: 2012-05-05
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
Comment
Question by:SherryRzv
12 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 22975394
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

by:Jeews
ID: 22975467
=IF(B1=1,C1,IF(B1=4,C1,IF(B1=5,C1,A1)))
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22975476
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 22975720
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
 

Author Comment

by:SherryRzv
ID: 22975749
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 93

Expert Comment

by:Patrick Matthews
ID: 22975803
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

by:SherryRzv
ID: 22976120
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 93

Expert Comment

by:Patrick Matthews
ID: 22976145
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
 

Author Comment

by:SherryRzv
ID: 22976193
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 93

Expert Comment

by:Patrick Matthews
ID: 22976293
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

by:SherryRzv
ID: 22976970
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

by:SherryRzv
ID: 31517427
You have to understand the coding, it only for intermediate to understand it.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question