Question about a NOT SO SIMPLE Macro

[sssb2000]

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
	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
17	Starting#	Goal#	R1	R2	R3	R4	R5	R6	Var.%	Final#
18	6	3					1			1
19						1				1
20								1		1
21				1						1
22							1			1
23			1							1
Sheet1

 

[Cbrine]

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

 

[sssb2000]

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

 

[martinee]

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.

 

[Cbrine]

Try this

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

 

[Cbrine]

Try this

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

 

[sssb2000]

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

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

 

[martinee]

If I'm understanding you, this simplifies your nested IF's.

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.

For Each c In Range("range1")
....

HTH.

 

[sssb2000]

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!

 

[martinee]

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.