[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Do while loop error....

guy

12/11/2006 6:59:00 PM

hello, i am a beginner...Thank you for your help!!

the inner loop was highlighted (compile error), but i don't know what's
wrong here
ps: i want to map the price in J5:J25 to the corresponding cells in
E5:E19...

_______________________________________________________________________________________
Sub MapPrice()

Dim i, j As Integer
Dim selection_v, selection_p As Range

i = 5
j = 5
selection_v = Range("C5:E19")
selection_p = Range("H5:J25")

Do While j <= selection_v.Rows.Count + 4

Do While i <= selection_p.Rows.Count + 4

If Range("H" & i).Value & Range("I" & i).Value = Range("C" &
j).Value & Range("D" & j).Value Then
Range("J" & i).Copy Destination:=Range("E" & j)

i = i + 1

Loop

If isBlank(Range("E" & j).Value) Then
Range("E" & j).Value = "Not found"

i = 5
j = j + 1

Loop

End Sub
5 Answers

Jim Thomlinson

12/11/2006 7:31:00 PM

0

Without looking too closely it appears as if your inner loop is missing an
end if...

If Range("H" & i).Value & Range("I" & i).Value = Range("C" &
j).Value & Range("D" & j).Value Then
Range("J" & i).Copy Destination:=Range("E" & j)
end if '???
--
HTH...

Jim Thomlinson


"guy" wrote:

> hello, i am a beginner...Thank you for your help!!
>
> the inner loop was highlighted (compile error), but i don't know what's
> wrong here
> ps: i want to map the price in J5:J25 to the corresponding cells in
> E5:E19...
>
> _______________________________________________________________________________________
> Sub MapPrice()
>
> Dim i, j As Integer
> Dim selection_v, selection_p As Range
>
> i = 5
> j = 5
> selection_v = Range("C5:E19")
> selection_p = Range("H5:J25")
>
> Do While j <= selection_v.Rows.Count + 4
>
> Do While i <= selection_p.Rows.Count + 4
>
> If Range("H" & i).Value & Range("I" & i).Value = Range("C" &
> j).Value & Range("D" & j).Value Then
> Range("J" & i).Copy Destination:=Range("E" & j)
>
> i = i + 1
>
> Loop
>
> If isBlank(Range("E" & j).Value) Then
> Range("E" & j).Value = "Not found"
>
> i = 5
> j = j + 1
>
> Loop
>
> End Sub
>
>
>

Jim Cone

12/11/2006 7:46:00 PM

0


You are missing "End If" in two places and "IsBlank" cannot be used
in VBA code. I substituted the Len function.
Row variables should be declared as a Long.
(you may want to do something in the bottom half of the sheet sometime)

It looks like you may not get the results you want; give it a try
and find out.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primiti...

Sub MapPrice()
Dim i As Long
Dim j As Long
Dim selection_v As Range, selection_p As Range

i = 5
j = 5
selection_v = Range("C5:E19")
selection_p = Range("H5:J25")

Do While j <= selection_v.Rows.Count + 4
Do While i <= selection_p.Rows.Count + 4
If Range("H" & i).Value & Range("I" & i).Value = _
Range("C" & j).Value & Range("D" & j).Value Then
Range("J" & i).Copy Destination:=Range("E" & j)
i = i + 1
End If
Loop

If Len(Range("E" & j).Value) > 0 Then
Range("E" & j).Value = "Not found"
i = 5
j = j + 1
End If
Loop
End Sub
'------------


"guy" <guy@email.com>
wrote in message
hello, i am a beginner...Thank you for your help!!
the inner loop was highlighted (compile error), but i don't know what's
wrong here
ps: i want to map the price in J5:J25 to the corresponding cells in
E5:E19...
____________
Sub MapPrice()

Dim i, j As Integer
Dim selection_v, selection_p As Range

i = 5
j = 5
selection_v = Range("C5:E19")
selection_p = Range("H5:J25")

Do While j <= selection_v.Rows.Count + 4

Do While i <= selection_p.Rows.Count + 4

If Range("H" & i).Value & Range("I" & i).Value = Range("C" &
j).Value & Range("D" & j).Value Then
Range("J" & i).Copy Destination:=Range("E" & j)

i = i + 1

Loop

If isBlank(Range("E" & j).Value) Then
Range("E" & j).Value = "Not found"

i = 5
j = j + 1

Loop

End Sub

Jim Cone

12/11/2006 7:49:00 PM

0

This line ...
If Len(Range("E" & j).Value) > 0 Then
Should be ...
If Len(Range("E" & j).Value) = 0 Then
--
Jim Cone

guy

12/12/2006 1:37:00 PM

0

Thanks!!
"Jim Cone" <jim.coneXXX@rcn.comXXX>
???????:ep$Sx1VHHHA.4904@TK2MSFTNGP04.phx.gbl...
> This line ...
> If Len(Range("E" & j).Value) > 0 Then
> Should be ...
> If Len(Range("E" & j).Value) = 0 Then
> --
> Jim Cone


guy

12/12/2006 1:37:00 PM

0

Thanks!!
"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> ¼¶¼g©ó¶l¥ó·s»D:49459835-012A-4138-9CDF-DB1CC71F1153@microsoft.com...
> Without looking too closely it appears as if your inner loop is missing an
> end if...
>
> If Range("H" & i).Value & Range("I" & i).Value = Range("C" &
> j).Value & Range("D" & j).Value Then
> Range("J" & i).Copy Destination:=Range("E" & j)
> end if '???
> --
> HTH...
>
> Jim Thomlinson
>
>
> "guy" wrote:
>
>> hello, i am a beginner...Thank you for your help!!
>>
>> the inner loop was highlighted (compile error), but i don't know what's
>> wrong here
>> ps: i want to map the price in J5:J25 to the corresponding cells in
>> E5:E19...
>>
>> _______________________________________________________________________________________
>> Sub MapPrice()
>>
>> Dim i, j As Integer
>> Dim selection_v, selection_p As Range
>>
>> i = 5
>> j = 5
>> selection_v = Range("C5:E19")
>> selection_p = Range("H5:J25")
>>
>> Do While j <= selection_v.Rows.Count + 4
>>
>> Do While i <= selection_p.Rows.Count + 4
>>
>> If Range("H" & i).Value & Range("I" & i).Value = Range("C" &
>> j).Value & Range("D" & j).Value Then
>> Range("J" & i).Copy Destination:=Range("E" & j)
>>
>> i = i + 1
>>
>> Loop
>>
>> If isBlank(Range("E" & j).Value) Then
>> Range("E" & j).Value = "Not found"
>>
>> i = 5
>> j = j + 1
>>
>> Loop
>>
>> End Sub
>>
>>
>>