PDA

View Full Version : Loop Problem



stenlake1
06-20-2007, 08:03 AM
Hi all,

A bit tougher question for you clever people... and then hopefully I can finally finish my script.

See attached spreadsheet. I want to go through column A (starting at "A2"), until a new value is found. So the first would be "862236". Once this value has been found, I want the user to be prompted:

"Would you like " & cell.value & " interpolated?"

The user then has to click "Yes" or "No". If they click yes, I want it to select the first appearance of the value and then do my script. If no, then to carry on the loop until it finds the next new value - "862237" or until there are no further values in Column A.

I have a basic construct of:

if <conditional statement of value comparison that yields true> then
if <prompt> = yes then
<other code>
end if
end if
next

Can anyone help me with this? Thanking you in advance.

Bob Phillips
06-20-2007, 08:16 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow
If Application.CountIf(.Range(.Range("A1"), .Cells(i, "A")), .Cells(i, "A")) = 1 Then
If MsgBox("Row " & i & ", value " & .Cells(i, "A").Value & vbNewLine & _
"Run the script?", vbYesNo) = vbYes Then
MsgBox "insert your code here"
End If
End If
Next i
End With

End Sub

stenlake1
06-20-2007, 08:34 AM
Thank you very much - before I run the next bit of script, I want the cell with the first appearance of a value to be selected i.e. "A2" for 862236. How do I do that? or "A13" for 862237. Any thoughts?

Bob Phillips
06-20-2007, 08:38 AM
Depends upon what you mean by selected. Don't forget the macro is still running, so it is best to branch off and run that other script from within the m acro. You know the relevant cell, so you can pass that.

stenlake1
06-20-2007, 08:43 AM
With ActiveSheet
kLastRow = .Cells(.Rows.Count, TEST2_COLUMN).End(xlUp).Row
For k = 2 To kLastRow
If Application.CountIf(.Range(.Range("A1"), .Cells(k, "A")), .Cells(k, "A")) = 1 Then
If MsgBox("Row " & k & ", value " & .Cells(k, "A").Value & vbNewLine & "Run the script?", vbYesNo) = vbYes Then

<first appearance of value e.g. "A2">.Select ****This is what i want to do

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.Offset(0, 1)).Select
Selection.Copy

Is it possible?

Bob Phillips
06-20-2007, 09:11 AM
Yes, although I wouldn't personally select anything, but what are you going to do with that copied data? If you carry on with the macro, it will get a new set copied the next time it finds a new entry and the user says yes.

stenlake1
06-20-2007, 09:16 AM
Sorry, I pasted a part of the script. The data is then pasted into a different worksheet and done all sorts to. How can you select the cell? Thanks xld, i really appreciate it.

Bob Phillips
06-20-2007, 09:45 AM
This will create a range object pointing at the dataset, and you can work on that



Set rng = .Cells(i, "A").Resize(iLastRow - i + 1, 2)

stenlake1
06-20-2007, 09:57 AM
Why is it always the last thing you do, causes the most problems?! :)

Right, I am guessing I am doing this the complete wrong way. I have attached the problem in an excel file (simplified).

Once a new k is found I want all the values in red (associated with k) copied, so I can paste into a new worksheet. This is for my education as I am new to this, but how would you do this?

Many thanks for all your help.

Bob Phillips
06-20-2007, 10:04 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow
If Application.CountIf(.Range(.Range("A1"), .Cells(i, "A")), .Cells(i, "A")) = 1 Then
If MsgBox("Row " & i & ", value " & .Cells(i, "A").Value & vbNewLine & _
"Run the script?", vbYesNo) = vbYes Then
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
sh.Name = .Cells(i, "A").Value
.Range(.Cells(i, "A"), .Cells(i, "A").End(xlDown)).Resize(, 2).Copy sh.Range("A1")
.Activate
End If
End If
Next i
End With

End Sub

stenlake1
06-20-2007, 10:18 AM
And how to I copy the associated data from columns "E" and "F" too? Thank you, this is very helpful :)

Bob Phillips
06-20-2007, 10:26 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow
If Application.CountIf(.Range(.Range("A1"), .Cells(i, "A")), .Cells(i, "A")) = 1 Then
If MsgBox("Row " & i & ", value " & .Cells(i, "A").Value & vbNewLine & _
"Run the script?", vbYesNo) = vbYes Then
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
sh.Name = .Cells(i, "A").Value
.Range(.Cells(i, "A"), .Cells(i, "A").End(xlDown)).Resize(, 2).Copy sh.Range("A1")
.Range(.Cells(i, "A"), .Cells(i, "A").End(xlDown)).Offset(0, 4).Resize(, 2).Copy sh.Range("E1")
.Activate
End If
End If
Next i
End With

End Sub

stenlake1
06-20-2007, 10:46 AM
Brilliant, and finally, after I have created the now worksheet, how do I select it at the end as the rest of the script uses the new one?

Bob Phillips
06-20-2007, 11:34 AM
sh.Activate

lucas
06-20-2007, 11:37 AM
Which new one...when I ran it there were several sheets added.

Bob Phillips
06-20-2007, 12:19 PM
Which do you want?

stenlake1
06-21-2007, 01:41 AM
Hello all,

No xld got the right one - the whole thing works well now and so I am just fiddling with it to make it appear more nicely! See my new post :)