Hi pete_me_a,
Try setting up a loop that runs the code for each name in the customer list.
Its very difficult for me to work with your code because I don't know where you're getting your information from for
the function parameters. I would need to build a whole worksheet in order to call the function.
I resorted to using an InputBox to get the name and product ID because I don't know how you get input from the user.
I would need to see the procedure that calls the function. You can change these lines to match your method for getting
user input.
Your goal is to get your function to look this:
'Get the value of each cell in the range that contains the names
For Each cell in range to number of rows in range
------ Code goes here ------
Next cell
Try something like the following. Again, I can't work with your function as I can't call it. I don't know
what the parameters mean. An example would be DHW_TRANSACTION_ONE. I assume this is the customer ID but not sure.
This code checks for three parameters as you can see. Its meant to perform this logic only as a starting
point but set up as a loop.
- Code: Select all
If (((DHW_TRANSACTION_TWO = DHW_TRANSACTION_ONE) _
And (Total_value_one = 0) _
And (PRODUCT_ID_TWO = PRODUCT_ID_ONE)) _
Try pasting this:
- Code: Select all
Sub Check_for_refund()
Dim lCell As Range
Dim Total_value As Integer
Dim Cust_Name As String
Dim PRODUCT_ID As String
Dim Get_refunds_exchange As String
Dim i As Long
'Get user input
Cust_Name = UCase(Application.InputBox("Enter name :", , , , , , 2))
PRODUCT_ID = UCase(Application.InputBox("Enter product ID :", , , , , , 2))
'You must change the range in next the line to match the range that contains
'the customer names. But you can use it with the example worksheet to test it.
For Each lCell In Range("E2:E10")
Total_value = UCase(lCell.Offset(0, -1))
'Check if its the same customer and Total_value = 0 and PRODUCT_ID is the same
If UCase(lCell.Value) = Cust_Name _
And Total_value = 0 _
And UCase(lCell.Offset(0, -2)) = PRODUCT_ID Then
Get_refunds_exchange = "Refund"
Else: Get_refunds_exchange = "No Refund!"
'Exit Sub
End If
MsgBox Get_refunds_exchange
Next lCell
End Sub
Run the following code in a new workbook to test the code above:
- Code: Select all
Sub BuildExampleSheet()
With ActiveSheet
Cells(1, 3) = "PRODUCT_ID"
Cells(1, 4) = "Total_value"
Cells(1, 5) = "Cust_Name"
Range("C2, C5, C8") = "Toys"
Range("C3, C6") = "Crafts"
Range("C4, C7, C9") = "Hardware"
Range("C10") = "Auto"
Range("D2, D3") = 0
Range("D4, D5") = 5.99
Range("D6:D10") = 0
Range("E2, E3") = "Mary"
Range("E4, E6, E10") = "Helen"
Range("E5, E7") = "Jim"
Range("E8, E9") = "Joe"
End With
End Sub
Then try enterig different in the cells to see if the logic is correct. If its not, please reply with what went wrong.
We can expand on this after the logic is correct.