Nested If/And Statement as a VBA code

[jm485]

I can't put an If/And formula into a cell because it has a validation list connected to it. So I need to figure out how to put the formula into a VBA code.

This is what I have come up with but it's not working...

Private Function FirstProcess(ByVal Target As Range)
If Range("F16").Value = Range("I11 to J11").Value Then
    Range("F17,F19,F21").Value = Range("G11").Value
 
ElseIf Range("F16").Value = Range("I12 to J12").Value Then
    Range("F17,F19,F21").Value = Range("G12").Value
 
ElseIf Range("F16").Value = Range("M10 to N10").Value Then
    Range("F17,F19,F21").Value = Range("K10").Value
 
ElseIf Range("F16").Value = Range("M11 to N11").Value Then
    Range("F17,F19,F21").Value = Range("K11").Value
 
ElseIf Range("F16").Value = Range("M12 to N12").Value Then
    Range("F17,F19,F21").Value = Range("K12").Value
 
Else
    Range("F17,F19,F21").Value = 0
 
End If
End Function

So what I need is if F16= date....

If date is between I11 to J11 then cell G11 will be copied into cells F17,F19,& F21

If date is between I12 to J12 then cell G12 will be copied into cells F17,F19, & F21

If date is between M10 to N10 then cell K10 will be copied into cells F17,F19, & F21

If date is between M11 to N11 then cell K11 will be copied into cells F17,F19, & F21

If date is between M12 to N12 then cell K12 will be copied into cells F17,F19, & F21

If date is blank then F17,F19 & F21 will be left blank.

 

[Jerry Sullivan]

A Select Case statement is usually best when you want to run several tests on a value and execute the first test that passes.

Something like this....

Sub FirstProcess()
    With Range("F17,F19,F21")
        Select Case Range("F16")
            Case Range("I11") To Range("J11")
                .value = Range("G11").value
            Case Range("I11") To Range("J11")
                .value = Range("G12").value
            Case Range("M10") To Range("N10")
                .value = Range("K10").value
            Case Range("M11") To Range("N11")
                .value = Range("K11").value
            Case Range("M12") To Range("N12")
                .value = Range("K12").value
            Case Else
                .value = 0
            End Select
    End With
End Sub

This creates a macro which could be run through Alt-F8 or a button or linked to a worksheet event. How do you want to trigger this macro?

 

[jm485]

I want it linked to a worksheet event. So once they enter the date in F16 then the macro will run.

 

[jm485]

Let me re-phrase that response since the person will not get to select a date in cell F16. It will be autofilled with a formula.

I do want the above code to be linked to a worksheet event. More than likely the cells G11, G12, that have validation lists would be the likely cell to trigger the macro?

So once they choose an option then cell F16-BR16 will have their 3 locations (F17,F19,F21-BR17,BR19,BR21) autofilled if their dates match the range entered into I11 to J11, etc

I've been working so long on these macros that my brain is starting to shut down! So I tried doing the code like this:

Sub FirstProcess(ByVal Target As Range)
If Not Intersect(Target, [G11]) Is Nothing And Target.Cells.Count > 0 Then
With Range("F17,F19,F21")
        Select Case Range("F16")
            Case Range("I11") To Range("J11")
                .Value = Range("G11").Value
            Case Range("I11") To Range("J11")
                .Value = Range("G12").Value
            Case Range("M10") To Range("N10")
                .Value = Range("K10").Value
            Case Range("M11") To Range("N11")
                .Value = Range("K11").Value
            Case Range("M12") To Range("N12")
                .Value = Range("K12").Value
            Case Else
                .Value = 0
            End Select
    End With
End If
End Sub

But it doesn't work.

 

[MrKowz]

You can't test a value in that manner. You have to explicitly state the conditions in each test:

Private Function FirstProcess(ByVal Target As Range)
If Range("F16").Value >= Range("I11").Value And Range("F16").Value <= Range("J11").Value Then
    Range("F17,F19,F21").Value = Range("G11").Value
 
ElseIf Range("F16").Value >= Range("I12").Value And Range("F16").Value <= Range("J12").Value Then
    Range("F17,F19,F21").Value = Range("G12").Value
 
ElseIf Range("F16").Value >= Range("M10").Value And Range("F16").Value <= Range("N10").Value Then
    Range("F17,F19,F21").Value = Range("K10").Value
 
ElseIf Range("F16").Value >= Range("M11").Value And Range("F16").Value <= Range("N11").Value Then
    Range("F17,F19,F21").Value = Range("K11").Value
 
ElseIf Range("F16").Value >= Range("M12").Value And Range("F16").Value <= Range("N12").Value Then
    Range("F17,F19,F21").Value = Range("K12").Value
 
Else
    Range("F17,F19,F21").Value = 0
 
End If
End Function

 

[jm485]

Thank you for that suggestion.

However, the code still doesn't work for me. Nothing happens. I think there is something else going on that makes it not work.

Do you think it has anything to do with the fact that cell F16 has a formula to give it it's date. So F16's formula reads =date1

H16 (which will also have the same above code applied to it as F16) also has a formula which reads =IF(F$16="",$N$7-($N$7-$L$7-1),IF(F$16>=$N$7,"",F$16+1))

N7 and L7 are the main departure /return dates which the rest of the cells with dates will be checked against to make sure they are within the range.

 

[MrKowz]

Can you please provide some data, as well as your desired outcome and the logic behind it?

 

[jm485]

If I could attach an example of my sheet it might make it easier. But I don't have permission to.

So I will do the best I can at explaining.

The worksheet is an expense report. The idea is to make it so everything is autofilled. They type in all the destinations that they will be going to and the dates they will be in that location at the top of the report.

Then the expense part of the document will autofill the destinations according to the date ranges they entered

Then all that needs to be done is to go through and delete the destination cells that they don't need for each day. Such as if on 3/4/11 they had breakfast but then traveled to another city just for lunch they would click on the destination cell under lunch and change it. They also have the option to delete it if they didn't have a certain meal that day.

There are 5 days listed on one sheet and then if more days are needed it expands into 5 more with 25 days being the max.

Departure Date-------------------- Return Date
L7 ---------------------------------N7
Dates in Destination Cells: ---------Main destinations:
I11 to J11 -------------------------G11
I12 to J12 -------------------------G12
M10 to N10 -------------------------K10
M11 to N11 -------------------------K11
M12 to N12 -------------------------K12

EXPENSE SECTION--------------------------------------------------

Date: _____F16_______ H16_________ J16________ L16_________ N16
breakfast __F17_______ H17_________ J17________ L17_________ N17
destination

lunch_____ F19_______ H19_________ J19________ L19__________ N19
destination

dinner ____F21_______ H21_________ J21_________ L21_________ N21
destination

I'm sorry if this looks confusing. I hope this is the explaination you wanted.

 

[jm485]

Ok so the code you gave me works when I run the macro!

So I think my problem is how to get the macro to run when a value is entered into cells: I11 to J11; I12 to J12; M10 to N10; M11 to N11; M12 to N12.

Sorry I was so slow to realize that your code works and gives me what I want.

 

[jm485]

I figured out which vba code to attach the above code to!

I just added it to the bottom of the code that runs to check to make sure the destination dates are within the range of the departure and return date.

I recorded a macro calling it Autofill_Destination and then opened it up and pasted your code into it. So the end of the code looks like this:

End If
Call Autofill_Destination
End Sub

I couldn't have done this without you, so thank you sooooo much!