Message box and mark by lookup in other sheet

[Panoos64]

Hi all, I would like to write a macro so that when i run it, to appears a message box with text ("Please Enter Group's Code"), and when i enter the group's code, automatically lookup through column "A" and marks the debtors' names whose are belong the code and also it's transactions in column "C" which contains text or numbers in column "B". The debtors groups are in another excel sheet in the following bath: c:/my documents/Groups. (Sheet Groups). In this sheet the debtors' names are in col. "A" and its' codes in col. "B". Note that the database sheet contains more than 35000 rows and the groups are more than 40 different codes. Please see below extracts spreadsheets for what i requested. Ignore any borders of the below spreadsheets. Any help or advice would be great appreciated. Best regards.

	F.1.
	A	B	C	D	E
1
2	Date	Reference		Debit	Credit
3
4	ABCD LTD
5
6	13/03/2014	INV-7890		1.499,40	0,00
7	19/03/2014	CNN-3487		-1.499,40	0,00
8
9			Totals	0,00	0,00
10
11	EFGH LTD
12
13	01/08/2008	REC-2567		0,00	948,75
14	20/08/2008	INV-8095		6.325,00	0,00
15	26/08/2008	REC-2675		0,00	6.325,00
16
17			Totals	6.325,00	7.273,75
18
19	IJKLM LTD
20
21	20/05/2013	REC-2750		0,00	619,50
22	23/12/2013	INV-8128		929,25	0,00
23	31/12/2013	REC-2880		0,00	929,25
24
25			Totals	929,25	1.548,75

<tbody>
</tbody>

	F.2.
	A	B	C	D	E
1	Date	Reference		Debit	Credit
2
3	ABCD LTD		10
4
5	13/03/2014	INV-7890	10	1.499,40	0,00
6	19/03/2014	CNN-3487	10	-1.499,40	0,00
7
8			Totals	0,00	0,00
9
10	EFGH LTD		10
11
12	01/08/2008	REC-2567	10	0,00	948,75
13	20/08/2008	INV-8095	10	6.325,00	0,00
14	26/08/2008	REC-2675	10	0,00	6.325,00
15
16			Totals	6.325,00	7.273,75
17
18	IJKLM LTD		10
19
20	20/05/2013	REC-2750	10	0,00	619,50
21	23/12/2013	INV-8128	10	929,25	0,00
22	31/12/2013	REC-2880	10	0,00	929,25
23
24			Totals	929,25	1.548,75
25

<tbody>
</tbody>

	F.3.
	A	B
1	Debtor's Name	Code
2	ABCD LTD	10
3	EFGH LTD	10
4	IJKLM LTD	10

<tbody>
</tbody>

 

[Panoos64]

Is it possible to create a formula using VLOOKUP in order to resolve my issue? I would be creat appreciated to advice me. Kind regards

 

[Panoos64]

Any advice how can i use formula VLookup so that to search and find information through other spreadsheet i would
greatly appreciated. Kind regards

 

[pmich]

Since no one has so far replied, here is my suggestion.
Try this and if found useful, alter as needed.
Sheet 1
No Name Pin
416 David 602172
412 Stephen 603107
654 Barnabas 603152
361 Chandran 603082


Sheet 2
No Name Pin
416 David 602172
654 Barnabas 603152
414 Abraham 603118


Copy this Function in a Module.

Function SafeVlookup(lookup_value, _
                        range_lookup, col_index, error_value) As Variant
'http://stackoverflow.com/questions/17100782/vba-excel-vlookup-crashes-my-program-when-no-match-found
'http://www.cpearson.com/excel/errorhandling.htm
    On Error Resume Next
    Err.Clear
    return_value = Application.WorksheetFunction.VLookup(lookup_value, _
                        range_lookup, col_index, error_value)
    'http://www.mrexcel.com/forum/excel-questions/644608-vlookup-where-number-stored-text.html
    'If number is stored as text
    'return_value=VLOOKUP(TEXT(A50,0),'COS recon'!A:C,2,0)
    If Err <> 0 Then
      return_value = error_value
    End If
    SafeVlookup = return_value
    On Error GoTo 0
End Function

In your Userform, copy this.

Private Sub cmdVLookUpinMultipleSheets_Click()
 Dim SrchRng As Range
 Dim SrchVal As Range
dim SrchPin as long
 Sheets("Sheet1").Select
 Range("A2").Select
 Set SrchVal = Cells(5, 1)    'value in Active Row 5 & Col 1
 Set SrchRng = Range("Sheet2!A2:A4")
 SrchPin = SafeVlookup(SrchVal, SrchRng, 3, False)
 MsgBox "SrchPin=" & SrchPin & "."
End Sub

Hope this helps.

 

[Panoos64]

Hi pmich, it blocks over the above command on the words SafeVlookup "SrchPin = SafeVlookup(SrchVal, SrchRng, 3, False)"
My main point is that to create such macro so that to read information from another spreadsheet and especially the debtors groups which i separate them by codes e.g 5 names of debtors are belong code 10, other 7 names of debtors are belong the code 25 and so on. So then when i download the database and run the macro, should appears a message so that to enter the group code so that to mark the names of debtors whose are belong the code i entered and in addition each transaction which are belong the marked debtors names.For any additional information please do not hesitate to contact me. However many thanks for your respond. Best regards.

 

[pmich]

SafeVlookup is a Function. I have given you the code for this Function, which I got from Net. Have you copied it in a Module?
If so, run the code in your UserForm and press the command button "cmdVLookUpinMultipleSheets".
It will trigger cmdVLookUpinMultipleSheets_Click.
This will store data in SrchVal from Cells(5, 1) of Sheet1 which is value in Row 5 & Col 1.
SrchVal is searched in the Range A2:A4 of Sheet2 and the result is displayed in a message box.
(Later on change the code manually or by using a DO LOOP to store in SrchVal some other value from Sheet1 to search in Sheet2.)

 

[Panoos64]

Hi again pmich, something i am not do it right so the function dosen't work. I am sure that your instructions are correct but due to i am not so expert in VB is the reason that the function dosen't work. The fault is mine. Also i found that is complicate function and i don't want to discomfort you any more. I prefer to find the solution for my issue using the formula vlookup, which should search through other spreadsheet named "Groups" and so i think i will resolve my issue. This formula i will assing then to a macro command and the only manual work which i will do is just to enter the group's code in the cell "A1" in the database which i download. However great and many thanks for your help. Is very kind of you and i would appreciated to help so that to write in correct syntax the above formula. Best regards.

 

[pmich]

Okay.