Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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. *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Chris Hankin" wrote in message ... 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. *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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. *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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. *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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? -- Regards, Nigel "Chris Hankin" wrote in message ... 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. *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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. *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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 -- Regards, Nigel "Chris" wrote in message ... 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. *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
Sub evaluatesumproduct()
myval = InputBox("Enter acct num") Range("d1").Value = Application. _ Evaluate("=SUMPRODUCT((A2:A7=" & myval & ")*M2:N7)") End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Chris Hankin" wrote in message ... 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. *** Sent via Developersdex http://www.developersdex.com *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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. *** Sent via Developersdex http://www.developersdex.com *** |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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 *** Sent via Developersdex http://www.developersdex.com *** |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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 -- Regards, Nigel "Chris" wrote in message ... 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. *** Sent via Developersdex http://www.developersdex.com *** |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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. *** Sent via Developersdex http://www.developersdex.com *** |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Chris" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup finance help please
Thanks Don for all your excellent help - greatly appreciated. kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Google finance | Excel Programming | |||
finance functions not available | Excel Worksheet Functions | |||
Excel and Finance | Excel Programming | |||
Finance Formula Help | Excel Worksheet Functions | |||
finance functions | Excel Programming |