Vlookup finance help please

[Chris Hankin]

Hello, could someone please help me with the following:

In my worksheet (Named: Register) I need a Vlookup macro that asks the
user for a File Reference Number (via Input Box).

All the File Reference Numbers are in column A.

I need Vlookup to lookup columns M and N and sum all the values that are
associated with the User's File Reference Number, and then place the sum
in columns Q and R respectively - adjacent to the last User entered File
Reference Number in column A.

Example: Column A contains 5-references to the User entered File
Reference Number (Cells A8:A12), I need Vlookup to sum the values in
(Cells M8: M12) and places this figure into cell Q12. Also I need
Vlookup to sum the values in (cells N8:N12) and place this figure into
cell R12.

Any help would be greatly appreciated,

Kind regards,

Chris.

 

[Nigel]

Not really a VLookup question, but have you looked at using autofilter, to
filter the users selection in column A, then add a subtotal to columns M and
N. These will give you the totals for the filtered list which can be
dropped into the last visible row in column Q and R.

 

[Don Guillett]

try this idea

Sub sumifem()
myval = InputBox("Enter acct num")
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("d1").Value = Application. _
SumIf(Range("a2:a" & lr), myval, Range("b2:b" & lr))
End Sub

 

[Chris Hankin]

Hi Don, thanks for you help - just having difficulties getting your code
to do what I need it to do.

Sub sumifem()
myval = InputBox("Enter acct num")
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("d1").Value = Application. _
SumIf(Range("a2:a" & lr), myval, Range("b2:b" & lr))
End Sub

I think the difficulty is in the the following area of your code:

Range("d1").Value = Application. _
SumIf(Range("a2:a" & lr), myval, Range("b2:b" & lr))

I need it to sum the values in columns M and N respectively and then
place that summed values in to columns Q and R, adjacent to the last
User entered File Reference Number.

Example: if the File Reference Number in column A was: ABC123 and there
were 5 instances of this same number, then I need Excel to sum up all
the values that exist in columns M and N for this File Reference Number.
Just by way of explanation, let us assume that File Reference Number:
ABC123 is located in cells: A8:A12. I would then need Excel to sum up
the corresponding values in cells: M8:M12 and N8:N12 respectively. The
sum of cells: M8:M12 needs to be placed in cell: Q12. The sum of cells:
N8:N12 needs to be placed in cell R12.

I hope this helps explain what I am attempting to achieve.

If you could provide further assistance, that would be very much
appreciated.

kind regards,

Chris.

 

[Chris Hankin]

Hi Nigel, thanks for you help. Please note that I do not wish to use
auto-filtering for this particular request.

Here is an example: if the File Reference Number in column A was: ABC123
and there
were 5 instances of this same number, then I need Excel to sum up all
the values that exist in columns M and N for this File Reference Number.
Just by way of explanation, let us assume that File Reference Number:
ABC123 is located in cells: A8:A12. I would then need Excel to sum up
the corresponding values in cells: M8:M12 and N8:N12 respectively. The
sum of cells: M8:M12 needs to be placed in cell: Q12. The sum of cells:
N8:N12 needs to be placed in cell R12.

I hope this helps explain what I am attempting to achieve.

If you could provide further assistance, that would be very much
appreciated.

kind regards,

Chris.

 

[Nigel]

Well autofilter would achieve what you desire, but if you do not wish to use
it then the other option would be to scan the list for a reference number
input and sum columns M & N until the end of this input value.

Can you confirm that reference numbers are sorted in order, or does that no
matter?

 

[Chris]

Hi Nigel, thanks for replying. The File Reference Numbers are in order.
The File Reference Numbers are entered in in groups.

For example:

I may enter in File Reference Number: FTAN52147 in column A six times if
it relates to travel. So, File Reference Number: FTAN52147 is showing
in range: A196:A201.

Thus, the File Reference Number: FTAN52147 is all bunched together in
order. Overall, the File Reference Numbers may not be in order in
column A.

For example:

File Reference Number: FTAN52148 is in range: A78:A83. However, all
instances of File Reference Number: FTAN52148 are all bunched together.

I hope this makes sense.

Kind regards,

Chris.

 

[Nigel]

The following code does what you want, paste the code behind the sheet you
wish to act upon.

Sub FindandSum()
Dim lR As Long
Dim rngF As Range
Dim sInput As String
Dim dblM As Double
Dim dblN As Double
sInput = Trim(InputBox("Enter Reference Number", "Title in Here"))
If Len(sInput) > 0 Then
Set rngF = Cells.Find(What:=sInput, _
After:=Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:= False, _
SearchFormat:=False)
If Not rngF Is Nothing Then
lR = rngF.Row
Do While sInput = Trim(Cells(lR, "A"))
dblM = dblM + Cells(lR, "M")
dblN = dblN + Cells(lR, "N")
lR = lR + 1
Loop
Cells(lR - 1, "Q") = dblM
Cells(lR - 1, "R") = dblN
End If
End If
End Sub

 

[Don Guillett]

Sub evaluatesumproduct()
myval = InputBox("Enter acct num")
Range("d1").Value = Application. _
Evaluate("=SUMPRODUCT((A2:A7=" & myval & ")*M2:N7)")
End Sub

 

[Chris]

Hi Nigel, thanks so much for the code - greatly appreciated. It does
work if I use numbers only for the File Reference Number in column A.
When I enter text such as "FTAN52147" it does not work for some reason.
Could you please modify your code so that it works for string data?

Thanks,

Chris.

 

[Chris]

Hi Don, thanks for your reply, but unfortunately your VBA code does not
seem to work at all. Nigel provided me with the following code below:

It works well if the File Reference Number is a number. However, I need
the File Reference Number to be text (example: FTAN52147).

Any further assistance that you or Nigel can give me would be very much
appreciated.

Kind regards,

Chris.

Sub FindandSum()
Dim lR As Long
Dim rngF As Range
Dim sInput As String
Dim dblM As Double
Dim dblN As Double
sInput = Trim(InputBox("Enter Reference Number", "Title in Here"))
If Len(sInput) > 0 Then
Set rngF = Cells.Find(What:=sInput, _
After:=Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:= False, _
SearchFormat:=False)
If Not rngF Is Nothing Then
lR = rngF.Row
Do While sInput = Trim(Cells(lR, "A"))
dblM = dblM + Cells(lR, "M")
dblN = dblN + Cells(lR, "N")
lR = lR + 1
Loop
Cells(lR - 1, "Q") = dblM
Cells(lR - 1, "R") = dblN
End If
End If
End Sub

 

[Nigel]

It does work for both numeric's and strings, however you must have spaces at
the end of your string entries, these are part of the reference. Find will
look for an exact match including spaces.

Either remove the trailing spaces from your reference code, or the procedure
will need to be changed

 

[Chris]

Hi Nigel,

I removed all the spaces from the File Reference Numbers and then your
code worked well. Thanks again for your help,

Cheers,

Chris.

 

[Don Guillett]

This works for col A with your text and cols M:N with your numbers

Sub evaluatesumproductString1()
Dim myval, colm, coln
myval = InputBox("Enter acct num")
'myval = Range("b1")
colm = Application. _
SumIf(Range("A2:A22"), myval, Range("M2:m22"))
coln = Application. _
SumIf(Range("A2:A22"), myval, Range("n2:N22"))
Range("d1") = colm + coln
End Sub

 

[Chris]

Thanks Don for all your excellent help - greatly appreciated.

kind regards,

Chris.