# Question about a NOT SO SIMPLE Macro

#### sssb2000

##### Well-known Member
Hi,
I'm trying to figure this out but having some difficulty....

I have a Dynamic range (Range1), that looks at the numbers in AP (in this case AP18:AP23)
Starting number is the sum of Range1
Goal number is a number we're "trying" to reduce the "starting #" To!
i have a code that says: "If the numbers in Range1 are all 1 Then"

what i need to figure out is the following:

- Do until Starting# = Goal#
- "If the numbers in Range1 are all 1 Then"
- Offset 2 columns (go to the range1's column AN)
if there is a number in a cell, delete it
if not, move to the next offset and do the same.
if you move to a column and there are multiple 1s in different cells, the one appearing first should be deleted.

This process goes on until the sum of our Range1 (which is related to those numbers using a sum formula as you can see) equals our Goal#.

Did i draw a clear picture?
post.xls
AGAHAIAJAKALAMANAOAP
17Starting#Goal#R1R2R3R4R5R6Var.%Final#
1863   1 1
19  1 1
20   1 1
21 1   1
22  1 1
231   1
Sheet1

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
sssb,
I'm not exactly sure what you are looking for. One of the items is how to cycle through a range of cells. Here's an example of that.

Dim cell as range

For each cell in range("A1",range("A65535").end(xlup))
if c.value ="Whatever" then
cell.offset(0,4).value = "ThisOrThat"
End if
Next

HTH
Cal thank you for your reply.

you see, i have most of the code; the part that i do NOT have is:

Offset 2 columns (go to the range1's column AN)
if there is a number in a cell, delete it
if not, move to the next offset and do the same.
if you move to a column and there are multiple 1s in different cells, the one appearing first should be deleted

i have the Do loop and i have the "If the numbers in Range1 are all 1 Then"

i just need help with the process of the reduction I'm not clear on what you mean by:
Offset 2 Columns (go to the range1's column AN)
In the example data you posted, where would range1 be? If you are offsetting 2 columns to get to range1, where are you starting, and why? It might be helpful if you posted what your small example would look like if the macro completed as expected.

Try this

Code:
``````For each cell in range("A1",range("A65535").end(xlup))
if isnumeric(cell.offset(0,-2))  then cell.offset(0,-2).value =""
Next``````

as you code in the loop.

Based of Column AP, it checks the values in the cells 2 columns back to see if it is a number. Deletes the value if is a number. Then cycle to the next cell in AP.

HTH
Cal

Try this

Code:
``````For each cell in range("AP1",range("AP65535").end(xlup))
if isnumeric(cell.offset(0,-2))  then cell.offset(0,-2).value =""
Next``````

as you code in the loop.

Based of Column AP, it checks the values in the cells 2 columns back to see if it is a number. Deletes the value if is a number. Then cycle to the next cell in AP.

HTH
Cal

Range1 is the range of column AP (in this case AP18:Ap23 but this range is dynamic so can't be fixed)

this is what i tried to do....
but i'm running into problems with the "End IF" "Exit For" and the "Loop" components. Please advise Code:
``````Do Until ffstr = Goal

If rngAh.Cells.Count = Application.WorksheetFunction.Sum(Range1) _
And Application.WorksheetFunction.Max(Range1) = 1 Then

For Each Cell In Range1

If Cell.Offset(0, -2) = 1 Then
Cell.Offset(0, -2).Value = ""
Else
If Cell.Offset(0, -3) = 1 Then
Cell.Offset(0, -3).Value = ""
Else
If Cell.Offset(0, -4) = 1 Then
Cell.Offset(0, -4).Value = ""
Else
If Cell.Offset(0, -5) = 1 Then
Cell.Offset(0, -5).Value = ""
Else
If Cell.Offset(0, -6) = 1 Then
Cell.Offset(0, -6).Value = ""
End If
End If
End If
End If
End If

Exit For
End If

Next Cell
Loop``````

If I'm understanding you, this simplifies your nested IF's.
Code:
``````Sub tt()
Dim c As Range, range1 As Range

Do Until ffstr = Goal

If rngAh.Cells.Count = Application.WorksheetFunction.Sum(range1) _
And Application.WorksheetFunction.Max(range1) = 1 Then

For Each c In range1
For i = (c.Column - 1) To (c.Column - 5) Step -1
If Cells(c.Row, i).Value = 1 Then
Cells(c.Row, i).Value = ""
Exit For
End If
Next i
Next c

Loop

End Sub``````
However, you said range1 is a named range. Then the proper referencing in VBA would be.
Code:
``````For Each c In Range("range1")
....``````
HTH.

that code makes sense i do get an error though!
it says "LOOP WITHOUT DO" !!!!!

but i do have a "do" statement! i don't understand!
Also,i changed your line "For i = (c.Column - 1) To (c.Column - 5) Step -1" to "For i = (c.Column - 2) To (c.Column - 6) Step -1" cause the first column the code should look at is offset 2

Can you think why it's giving me that error? it beats me!

Yes, I didn't see you had an IF statement before the first for loop. VBE things you are still in an if statement and hence there is no "DO" loop within it. Add an End IF in the appropriate place to solve that error. Sorry. HTH.

*EDIT*
I was also thinking, you might have to Do Loop in the wrong place. As this code is set up now, it will loop all your cells in range1 before even checking to see if you've achieved your "Goal". Did you want the macro to quit as soon as the goal is achieved? Consider just using an if statement in the for loops each time a value is deleted to see if your goal is achieved, then just exit sub. Once all the 1's are deleted, it doesn't matter if you keeping looping through the cells due to your DO loop because there is nothing left to delete. Consider revising? Or perhaps I'm missing something in the code.

Replies
3
Views
176
Replies
8
Views
379
Replies
4
Views
450
Replies
1
Views
268
Replies
1
Views
683

### Forum statistics

1,203,617
Messages
6,056,312
Members
444,858
Latest member
ucbphd ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back