Do until loop not working 1

[Sardamil]

I'm creating a macro in excel. One of the tasks of the macro is to replace the value in a cell, until it reaches a cell with a certain value. I keep getting this message however: "Run-time error '1004' : Method 'Range' of object'_Global' failed"

' Select cell A22, *first line of data*.
Range("A22").Select
If Range("B4") = "802" Then
' Set Do loop to stop when an empty cell is reached.
Dim einde2 As String
einde2 = Range(ActiveCell).Value
Do Until einde2 = "ABC"
Range(ActiveCell.Address) = "XYZ"
einde2 = Range(ActiveCell).Value
Loop
End If

It is probably a very basic mistake, but for a novice like me any help is much appreciated.

Anything that can go wrong will go wrong

Window to my world

 

[combo]

In [tt]einde2 = Range(ActiveCell).Value[/tt] either ActiveCell should contain proper address text or, if you plan to refer to ActiveCell, use it directly: [tt]einde2 = ActiveCell.Value[/tt].
You can also simplify line above loop to: [tt]einde2 = ActiveCell.Value[/tt]

combo

 

[SkipVought]

Hi,

Many problems...

'Select cell A22, *first line of data*.
Range("A22").Select
If Range("B4") = "802" Then
' Set Do loop to stop when an [b]empty cell[/b] is reached.
'[highlight]BUT, you loop Until einde2 = "ABC"[/highlight]
Dim einde2 As String
einde2 = Range(ActiveCell).Value   ' ActiveCell is a range already!
Do Until einde2 = "ABC"            ' not sure why this
Range(ActiveCell.Address) = "XYZ"  ' ActiveCell is a range
einde2 = Range(ActiveCell).Value   ' einde2 will always have "XYZ" !!!???
Loop
End If

Here's how I would code...

'
   Dim rng as Range, r as Range

   Set rng = Range("A22")
   Set rng = Range(rng, rng.End(xlDown))

   If Range("B4") = "802" Then
      For Each r in rng
         r.value = "ABC"
      Next
   End If

Here's your code modified...

'Select cell A22, *first line of data*.
Dim einde2 As String
Range("A22").Select
If Range("B4") = "802" Then
' Set Do loop to stop when an empty cell is reached. Really????

einde2 = ActiveCell.Value
Do Until einde2 = "ABC"          '???????
einde2 = ActiveCell.Value        'New statement location
ActiveCell.Value = "XYZ"
ActiveCell.Offset(1).Select.     'Select the next cell (klunky way to get there)
Loop
End If

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Sardamil]

Hi Skip,

thanks for your elaborate reply.
I tried using your code, but it doesn't work yet. The loop doesn't end when it reaches a cell with value ABC.

When I use your code, every cell in the range gets replaced by value XYZ, until an empty cell is reached. I would like to make it stop when it reaches value ABC. Or alternatively I would like it to stop when the value in the cell is not equal to "text1" or "text2".

'
Dim rng as Range, r as Range

Set rng = Range("A22")
Set rng = Range(rng, rng.End(xlDown))

If Range("B4") = "802" Then
For Each r in rng
r.value = "XYZ"
Next
End If

Anything that can go wrong will go wrong

 

[SkipVought]

That was my point. I'm trying to figure out what you want, but I'm getting mixed signals.

' Set Do loop to stop when an empty cell is reached.
'BUT, you loop Until einde2 = "ABC"

Is it an "empty cell" or is it a cell with "ABC"????

Well I GUESSED (YOU forced me to pick one or the other--OR [and this came to mind] is there REALLY some OTHER unstated criteria???) and it seems, from your last reply, that I put my money on the wrong horse.

So before I waste more of my time (and I'm retired, sitting in my sun room sipping my morning coffee, listening to Ravi Zacharizas and answering a Tek-Tips post) I would like you to 1) post an example of the data list your code is processing and 2) restate you requirements clearly, concisely, completely sans contradiction.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zelgar]

The problem is the code doesn't revise einde2 after the offset. Modify the macro as follows:

'Select cell A22, *first line of data*.
Dim einde2 As String
Range("A22").Select
If Range("B4") = "802" Then
' Set Do loop to stop when an empty cell is reached. Really????

einde2 = ActiveCell.Value
Do Until einde2 = "ABC"          '???????
[s]einde2 = ActiveCell.Value        'New statement location
[/s]ActiveCell.Value = "XYZ"
ActiveCell.Offset(1).Select.     'Select the next cell (klunky way to get there)
[b]einde2 = ActiveCell.Value        'New statement location
[/b]Loop
End If

 

[Sardamil]

I'm sorry if I confused you. Here's a part of the spreadsheet. The client number is in column B. All other values are in column A.

client 68294
client 68294
klant 68294

VERWERKTE AANTALLEN/BEDRAGEN

I want to replace the cells in column A while the value is client or klant. The value should be XYZ
Or alternatively until it the value in the cell is VERWERKTE AANTALLEN/BEDRAGEN.
This is the desired result:

XYZ 68294
XYZ 68294
XYZ 68294

VERWERKTE AANTALLEN/BEDRAGEN

Anything that can go wrong will go wrong

 

[Andrzejek]

if this is what you have:
[pre]
A B
1 Col_H1 Col_H2
2 client 68294
3 client 68294
4 klant 68294
5
6 VERWERKTE AANTALLEN/BEDRAGEN
[/pre]
Assuming you have column headers in row 1, how about:

Dim inrR As Integer
intR = 2   [green]'Start at row 2[/green]

Do While Range("A" & intR).Value <> ""
    If Range("A" & intR).Value = "client" Or _
       Range("A" & intR).Value = "klant" Then
           Range("A" & intR).Value = "XYZ"
    End If

    intR = intR + 1
Loop

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Sardamil]

HI Andy,

I changed your code to this, but that did the trick. Thanks.

' Select cell A22, *first line of data*.
Range("A22").Select
If Range("B4") = "802" Then
' Set Do loop to stop when an empty cell is reached.
Dim inrR As Integer
intR = 22 'Start at row 2

Do While Range("A" & intR).Value <> ""
If Range("A" & intR).Value = "client" Or _
Range("A" & intR).Value = "klant" Then
Range("A" & intR).Value = "XYZ"
End If

intR = intR + 1
Loop
End If

Anything that can go wrong will go wrong

 

[Andrzejek]

Sardamil,
You should really stat using TGML tags to present your code the right way. It is a lot easier to read.

[green]' Select cell A22, *first line of data*.[/green]
Range("A22").Select  [red]Why this line? What does it do?[/red]
If Range("B4") = "802" Then
[red]Do you really have a value/text of [b]802[/b] in cell B4?[/red]
    [green]' Set Do loop to stop when an empty cell is reached.[/green]
    Dim inrR As Integer
    intR = 22 [green]'Start at row 2[/green] [red]Wrong - start in row [u]22[/u][/red]

    Do While Range("A" & intR).Value <> ""
        If Range("A" & intR).Value = "client" Or _
           Range("A" & intR).Value = "klant" Then
               Range("A" & intR).Value = "XYZ"
        End If

        intR = intR + 1
    Loop
End If

Have fun.

---- Andy

There is a great need for a sarcasm font.