INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Do until loop not working

Do until loop not working

(OP)
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.

Quote (Murphy's Law)

Anything that can go wrong will go wrong

Window to my world

RE: Do until loop not working

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

combo

RE: Do until loop not working

Hi,

Many problems...

CODE

'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.
'BUT, you loop Until einde2 = "ABC"
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...

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...

CODE

'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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Do until loop not working

(OP)
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

Quote (Murphy's Law)

Anything that can go wrong will go wrong

RE: Do until loop not working

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Do until loop not working

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

CODE -->

'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)
einde2 = ActiveCell.Value        'New statement location
Loop
End If 

RE: Do until loop not working

(OP)
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

Quote (Murphy's Law)

Anything that can go wrong will go wrong

RE: Do until loop not working

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

CODE

Dim inrR As Integer
intR = 2   '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 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Do until loop not working

(OP)
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

Quote (Murphy's Law)

Anything that can go wrong will go wrong

RE: Do until loop not working

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

CODE

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

    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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close