Finding two variables on the same row to match function

[Werner]

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]

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]

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