Do Until....LOOP

[ajetrumpet]

hi guys,

If anyone is looking at this that read my last post here, I finally figured out that I cannot split column widths on a page. Life goes on I guess...

I wonder if someone can provide me a little snippet for my problem on this one....currently I have the following...

Sub RhondaWork()

Dim LOOPROW As Long

LOOPROW = 2

Do Until Len(Cells(LOOPROW, 7).Value) = 0
  If Cells(LOOPROW, 7).Value < Cells(LOOPROW, 8).Value Then
    Cells(LOOPROW, 7).Value = 0
  End If
  LOOPROW = LOOPROW + 1
Loop

End Sub

I'm not sure if there is ever going to be a break in the records of the sheet (as in "no data for the seventh column)....so I need to get rid of this...I have to substitute RANGE for CELLS, but I'm not quite sure how to finish...here is what I have so far...

Sub RhondaWork2()

  If Range("G2") < Range("H2") Then
  Range("G2") = 0
[color=red]"what goes here"??[/color]
End If

End Sub

Can someone give me a boost please?? Do I even need to mess with the range, or should I be looking at maybe "For, Next"?? I have a module that uses RANGE(["range"].End(xldown)) to specify the length of a column in another worksheet module, but I'm not sure if I can use that here. Thanks!

 

[unmarkedhelicopter]

hi guys,

If anyone is looking at this that read my last post here, I finally figured out that I cannot split column widths on a page. Life goes on I guess...

I wonder if someone can provide me a little snippet for my problem on this one....currently I have the following...

Sub RhondaWork()

Dim LOOPROW As Long

LOOPROW = 2

Do Until Len(Cells(LOOPROW, 7).Value) = 0
  If Cells(LOOPROW, 7).Value < Cells(LOOPROW, 8).Value Then
    Cells(LOOPROW, 7).Value = 0
  End If
  LOOPROW = LOOPROW + 1
Loop

End Sub

I'm not sure if there is ever going to be a break in the records of the sheet (as in "no data for the seventh column)....so I need to get rid of this...I have to substitute RANGE for CELLS, but I'm not quite sure how to finish...here is what I have so far...

Sub RhondaWork2()

  If Range("G2") < Range("H2") Then
  Range("G2") = 0
[color=red]"what goes here"??[/color]
End If

End Sub

Can someone give me a boost please?? Do I even need to mess with the range, or should I be looking at maybe "For, Next"?? I have a module that uses RANGE(["range"].End(xldown)) to specify the length of a column in another worksheet module, but I'm not sure if I can use that here. Thanks!

I assume you are trying to replicate the functionality of your first sub in your second ? but if you hard code ranges like this "G2", "H2" etc. then you will have to "hard code" every single cell you wish to consider. If you don't have a break in your data (with first sub) it will just go down to about the 65,000th row before falling over (maybe a hell of a lot later if its Excel 2007) You say "what goes here" the answer is almost anything. What do you want it to do ? I assume exit your loop but for what reason ? Row > 20,000 ? Cell colour is blue ? Text is red ?

 

[ajetrumpet]

The first loop stops when it reaches a cell in column 7 with 0 characters, it doesn't go to row 65,535 sir (and yes, it's Excel 2007). So, it's not that long. And yes, I want to do the same thing with the range sub as with the cell sub. I know the hardcoding is not the way to go, that's obvious. Can you not help me with this??

 

[unmarkedhelicopter]

The first loop stops when it reaches a cell in column 7 with 0 characters, it doesn't go to row 65,535 sir (and yes, it's Excel 2007). So, it's not that long. And yes, I want to do the same thing with the range sub as with the cell sub. I know the hardcoding is not the way to go, that's obvious. Can you not help me with this??

I must be really dense. What you asked for is exactly what your first sub does. If there is a problem with it waiting for a cell in column G to be empty you have not identified it. What other condition do you wish to impose ?
If we start by looking at column G and process that to find an empty cell and then loop within that as a given range, it's just the same as your first sub, except slower

 

[ajetrumpet]

I don't think you're understanding the idea I'm trying to get at....(the substitute of the "range" property for the "cells" property). Maybe this will help; what would you say is the difference between these two subs (in terms of the "data" that it manipulates)???

Sub1()

Dim LOOPROW As Long

LOOPROW = 2

Do Until Len(Cells(LOOPROW, 7).Value) = 0
  If Cells(LOOPROW, 7).Value < Cells(LOOPROW, 8).Value Then
    Cells(LOOPROW, 7).Value = 0
  End If
  LOOPROW = LOOPROW + 1
Loop

End Sub

Sub2()

Dim r As Range

  For Each r In Range([C1], [H1].End(xlDown))
    With r
      If IsNumeric(.Value) Then .Value = "'" & .Value
    End With
  Next

End Sub

So, the second sub ignores NULL and BLANK while looping through the cells in the range, when the first sub does not, it comes to a screeching halt as soon as it finds a null or blank in the loop, and if that null or blank comes before the end of the data range to be manipulated, the rest of the data does not receive the condition. See what I mean now???

By the way, we might be on different pages here...after all, I am not a programmer, and I'm not sure if you are.

And to respond to your last thread....YES, my request for a code snippet is to perform the exact same action and condition that the first sub will already do for me. Hopefully you see now, with the above explanation, the need and the purpose for the change.

 

[chergh]

after all, I am not a programmer, and I'm not sure if you are.

Good move insulting someone trying to help you. After reading it many times I think I have eventually deciphered what you are trying to do and it really isn't to hard but given your attitude I'm not inclined to help.

 

[unmarkedhelicopter]

I don't think you're understanding the idea I'm trying to get at....(the substitute of the "range" property for the "cells" property).

No, I'm not.

Maybe this will help; what would you say is the difference between these two subs (in terms of the "data" that it manipulates)???

So, the second sub ignores NULL and BLANK while looping through the cells in the range, when the first sub does not, it comes to a screeching halt as soon as it finds a null or blank in the loop, and if that null or blank comes before the end of the data range to be manipulated, the rest of the data does not receive the condition. See what I mean now???

No, as your range selection technique is pretty much the same.

By the way, we might be on different pages here...after all, I am not a programmer, and I'm not sure if you are.

So you think that may be why you can't explain what you want and similary I can't understand it ???

And to respond to your last thread....YES, my request for a code snippet is to perform the exact same action and condition that the first sub will already do for me. Hopefully you see now, with the above explanation, the need and the purpose for the change.

It was a post not a thread, a thread is the whole conversation. If that's what you want then try this :-

Sub WithRange1()
Dim r As Range
  For Each r In Range(Range("G1"), Range("G1").End(xlDown))
    With r
      If .Value < .Offset(0, 1).Value Then .Value = 0
    End With
  Next
End Sub

Don't use evaluate ([]), it is not good practice and generally slows your code down. If you want it to process right on through any gaps then you'd need to change your selection.

Sub WithRange2()
Dim r As Range
  For Each r In Range(Range("G1"), Range("G" & activesheet.rows.count).End(xlUp))
    With r
      If .Value < .Offset(0, 1).Value Then .Value = 0
    End With
  Next
End Sub

 

[ajetrumpet]

That's all I needed....the "offset and count"

Was that though?? I just thought I would take a stab at why we weren't on the same page. I have listened to many conversations between programmers and the people they work for. I think you'd be amazed at how many of those I have seen end without anything being accomplished simply due to language barriers. It's almost like listening to a Mandarin Chinese man speak his native language to a French man and expect him to understand (sorry, that's the best comparison I can come up with!)

 

[Brianwarnock]

Good move insulting someone trying to help you. After reading it many times I think I have eventually deciphered what you are trying to do and it really isn't to hard but given your attitude I'm not inclined to help.

Its not the first time AJE has shown that sort of attitude I also have decided I don't want to be insulted for not understanding him.

Brian

 

[unmarkedhelicopter]

Well ... having read his signiture ... either he hasn't ... or he doesn't understand it ... and/or he is just an abrasive character ... I'm abrasive too (sometimes ) ... but I think this is the last time I'll bother with him.

If ANYONE can point out how we were supposed to get to post #7 from post #1 and #3 in this thread ... then I'll be impressed.

And if he only needed 'offset' and 'count' then what I'd suggest; is buying a book, something with "dummies" in the title springs to mind.

 

[ajetrumpet]

Do both of you guys work in the IT field??

Don't take it personally, but if you do, the frustration of your jobs REALLY shows when you post replies. Is it really that bad??

"That's the last time I'll bother with him"???

I would say that's not the best way to work with people, but as I have told Brian before, I will not start a fire here (yet again).

Sorry you feel that way Brian. That's really too bad for both of us.

Oh, and helicopter, what's wrong with my signature?? Does it not fit into the IT world?? Is it too cheesy?? I laugh at it sometimes, but it figured it's "kind of" catchy at least.

 

[unmarkedhelicopter]

Your sig was fine, you just don't think it applies to you.
I see you've changed it, so that you don't have to explain my last comment.
My frustration stems from dealing with idiots and I can only think of one, that I've had to deal with in the last year or so.
It's good that you not start a fire but why are you rubbing those sticks together ?
I'm sure Brian laments the extensive help and assistance that he would have recieved from you if you and he had been on better terms

 

[chergh]

Well ... having read his signiture ... either he hasn't ... or he doesn't understand it ... and/or he is just an abrasive character ... I'm abrasive too (sometimes ) ... but I think this is the last time I'll bother with him.

If ANYONE can point out how we were supposed to get to post #7 from post #1 and #3 in this thread ... then I'll be impressed.

And if he only needed 'offset' and 'count' then what I'd suggest; is buying a book, something with "dummies" in the title springs to mind.

Well you've hit the nail on the head there. Ajet doesn't know how to explain what they want or how to get there but somehow it becomes our fault. They must be management.

 

[ajetrumpet]

I think you're right about that, there is too much blame going around isn't there??

I can't say I blame you for mentioning that.

But then again, you people should feel privaleged, you understand a subject that is very rarely understood by the majority of the population.

Even though you're frustrated, you should feel good about knowing this stuff and being able to offer your advice to people that are not as fortunate as you are. You might think I'm a jerk really, but after all, a lot of people, including me, admire people like you for "putting up with us" (the less knowledgable people)