Conditional Logic Macro

[SUSAN BAXTER]

Hi, first post. I was wondering if I could get help on developing a macro that would add dollar amounts in a range. I’ve looked at formulas but that will not work in this case.


In my research i found the following:
[h=1]Conditional Logic in VBA[/h] In the most of programming specific sections of code are executed depending on the result of one or more conditions. For checking a condition a series of keywords and operators that can be combined are provided. Checking a condition produces a True or a False result. Depending on the result the statements following the then are executed or passed over. The execution continues in the following branch – either in the else block (which is usually optional), or if there is no else branch, then after the end If. Conditional Statements are the If ....Then statement, with the Else or ElseIf, and the Select case statement.


I need the macro to search in the cell ranges G1:G100, K1:K100, P1:P100 (every cell is formatted as a numbers, 5.00, 7.00, 25.00 etc.). My condition for a true result would be the next two columns to the right of the range cell must have figures in them. Note: the first column to the right is formatted as text, if that matters, so column H,L and Q. The third columns I, M and R are formatted as numbers.


An example would be in cell G4 has the figure 25.00 in it, in cell H4 has a 2-25 in it and cell I15 has a 5.00 in it. Since cell G4 has two cells the right of it that have figures in them it would be a true condition. The macro would then search the cell range A1:A20,D1:D20 (which has numbers 1.00 to 40.00 in consecutive order) to match the number 25.00 (cell D25) and then write 5.00 from cell I4 to the cell to the right of it which is E5. After that continue through the rest of the range.


This will save me a ton of time. Thanks
<style type="text/css">h1 { margin-bottom: 0.21cm; direction: ltr; color: rgb(0, 0, 0); text-align: left; }h1.western { font-family: "Liberation Serif", sans-serif; }h1.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 24pt; }h1.ctl { font-family: "Lohit Devanagari"; font-size: 24pt; }p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[DanteAmor]

Try this

Sub Conditional_Logic()
    Dim r1 As Range, r2 As Range
    Dim c As Range, b As Range
    Set r1 = Range("A1:A20,D1:D20")
    Set r2 = Range("G1:G100,K1:K100,P1:P100")
    
    For Each c In r2.SpecialCells(xlCellTypeConstants, 23)
        If WorksheetFunction.CountA(c.Offset(, 1).Resize(, 2)) = 2 Then
            Set b = r1.Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not b Is Nothing Then
                b.Offset(, 1).Value = c.Offset(, 2).Value
            End If
        End If
    Next
    
    MsgBox "Done"
End Sub

 

[SUSAN BAXTER]

Hi Dante


Thanks so much for your reply. The code works really well. Can I ask you what the reference of 23 at the end of “xlCellTypeConstants, 23)’ is used for?


Many thanks for your assistance. It's greatly appreciated.
Susan
<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[DanteAmor]

Hi Dante


Thanks so much for your reply. The code works really well. Can I ask you what the reference of 23 at the end of “xlCellTypeConstants, 23)’ is used for?


Many thanks for your assistance. It's greatly appreciated.
Susan
<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

Code 23 is for selecting cells that contain any value

 

[SUSAN BAXTER]

Hi Mr. Amor

Thanks for your reply. I'm just about to post my last request titled "Data Block Code" and if you have time/interest it sure would wonderful if you're able to help. Your code is really good. Thanks again.
Susan<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.

 

[SUSAN BAXTER]

Hi Dante

Just wanted to keep you informed that another member here found some code which I looked at. It's close but there is a section where a number is entered on the Excel sheet (not sure what that is used for) and I can't use that. Is it possible to just delete a section of the code? I can post for you to see if it's possible I not so sure.

 

[DanteAmor]

Post your question and sure someone answers you.

 

[SUSAN BAXTER]

Hi Dante
I was wondering if you any time to look at some code that I found that I’m trying to use. I need to add 20 new data blocks and not sure if this the right code to use or maybe it’s me. The original code had two data blocks in it but when I go to add more I can’t get the additional ones to work. Just your opinion would be great. You can send me a private message or leave in my post which is “Data Block Code”. Would you use this code or look at something else? Thanks so much for your help.

<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[SUSAN BAXTER]

I forgot to mention it's the very last box in the post.