Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding two variables on the same row to match function
Hi, I have created a form which import data from another sheet for the purpose of updating the data and sending it back updated where it was. I need to create a search function to find and update the source sheet. Here's more details : Each row of the source list uses the two first columns to identify the row (which is unique in the list by using those two variables as combined identifiers). For example, collumn A = 114555 and the B = 104. Those two columns are in columns A and B in the source sheet. When I extract data, it place the value of column A in cell Y2 of the form (cause it's the same value for all the form's rows) and column B from the source becomes column A in the form. So, I need now to update the source with a "find next function" (when you find and copied take the next row in the form and search again) that uses the two identifiers from the form as a combination to find the source identifiers that are in the same row in the source sheet. Then, I want to copy cells A to AE from the form's row matching the source to source sheet's row in columns B to AF. Maybe I gave a bit too much details, so if you are a bit confused, feel free to ask all the questions that you have in mind. I'll answer as quickly as I can. Thx! Werner -- Werner ------------------------------------------------------------------------ Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304 View this thread: http://www.excelforum.com/showthread...hreadid=386164 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding two variables on the same row to match function
I think I need to explain a bit more. I give also the link to a copy of my work to help. ok allright, I've put online a copy of my Excel file. The source data is in "PI". The form is in "Formulaire". It represents a building and its apartments. So the logic is in the source sheet, column A represents each building identification and column B is the apartment number. So you understand that all the apartments in the same building have the same building identification in column A. In the form in sheet "Formulaire", when I extract data, I only show the building identification in one cell in cell Y2 cause it's always the same. so I've moved the data from one columns left. In the form, column A is the apartments numbers. Now, I need to send the apartments info updated back into the source sheet ("PI"). I need to use cell Y2 in "form" (building ID) as the first corresponding value in colunm A and his apartment number from column A in the form to colum B in the source sheet "PI". And I must do it for each apartment numbers (a row in the form is for an apartment) in other to update all the apartments in the building in the source data. Here's the link to my work : http://agora.ulaval.ca/~jjdou/Loclis04sample.zip If you need any more explanations, say it and I'll help you. Regards, Werner -- Werner ------------------------------------------------------------------------ Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304 View this thread: http://www.excelforum.com/showthread...hreadid=386164 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding two variables on the same row to match function
After a long try and error, I finally coded it myself hehe. For the sake of knowledge, here's the answer to the problem I described. Note that the first 2 loopings are there to calculate the dynamic range of rows in each of the two sheets. If anyone thinks there's something to improve in that code, feel free to tell me. I am always trying to code in a better way. Regards, Werner Code: -------------------- Sub Mise_a_jour_Sources() Dim e As Long Dim H As Long Dim L As Long Dim i As Long Dim j As Long ' Variable qui sert à savoir s'il y a des PI à supprimer. Dim Pi_Check As Long Pi_Check = 0 Dim lstRw As Long Dim lstRwForm As Long 'Vérification s'il y a des PI. With Worksheets("PI") For e = 16 To 25 If Range("A" & e).Value = "" Then 'Rien Else Pi_Check = Pi_Check + 1 End If Next e End With If Pi_Check = 0 Then lstRw = 8 Else 'Je détermine le nombre de ligne d'unité locative que j'ai. Let lstRw = Sheets("PI").Range("a65536").End(xlUp).Row End If Pi_Check = 0 With Worksheets("Formulaire") For H = 16 To 25 If Range("A" & H).Value = "" Then 'Rien Else Pi_Check = Pi_Check + 1 End If Next H End With If Pi_Check = 0 Then lstRw = 8 Else 'Je détermine le nombre de ligne d'unité locative que j'ai. Let lstRwForm = Sheets("Formulaire").Range("a65536").End(xlUp).Row MsgBox lstRwForm End If 'On met à jour l'information UE 'Acces_dossier Sheets("PI").Select Worksheets("PI").Range("A15").Select Dim g As Variant Dim find_rw As Variant Dim r As Variant Dim UERow As Range Dim PIRow As Variant PIRow = 0 Dim IDUform As Long Dim PIform As Variant Dim BoolRow As Boolean BoolRow = False Dim RangeData As Range 'Cette variable correspond à la ligne "Y2" du formulaire. IDUform = Worksheets("Formulaire").Range("Y2").Value With Worksheets("PI") Set RangeData = Range("A8:B" & lstRw) End With 'On met ensuite à jour l'information PI For L = 16 To lstRwForm PIform = Worksheets("Formulaire").Range("B" & lstRwForm).Value With RangeData Set UERow = .Find(IDUform, LookIn:=xlValues, lookat:=xlWhole) find_rw = UERow.Row Do While BoolRow = False If Worksheets("PI").Range("B" & find_rw).Value = _ Worksheets("Formulaire").Range("A" & L).Value Then BoolRow = True Else 'On continue Set UERow = .FindNext(UERow) find_rw = UERow.Row End If Loop End With MsgBox Worksheets("PI").Range("B" & find_rw).Value BoolRow = False Next L End Sub -------------------- -- Werner ------------------------------------------------------------------------ Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304 View this thread: http://www.excelforum.com/showthread...hreadid=386164 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can the Match function handle more than 7 variables? | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Index Match function for multiple linked variables | Excel Worksheet Functions | |||
Index Match function for multiple linked variables | Excel Worksheet Functions | |||
finding variables in a column | Excel Programming |