I have most of a macro but am having trouble with the goto command. I want to
use a specific reference in sheet 2 (it is the result of a formula that has
been copied as text to an adjacent column). The specific reference points to
a cell in sheet 1 that I am going to delete.

It is the "Application.Goto reference" line in the Do While...Loop I am
having trouble with - unless someone can think of a better way of getting the
result

How can I use the reference in sheet 2 in my macro without having to
actually type the text? Because typing it in the macro will "fix it" and make
it unchangeable which therefore will not felxible enough to use the
references below it.

Here is a small sample of the list of references I am trying to use in the
macro:
First row is the heading of the list and they are all in Column D of sheet 2:

Current Mail List
'Sheet 1'!$Q$5447
'Sheet 1'!$Q$12
'Sheet 1'!$Q$4
'Sheet 1'!$Q$16
'Sheet 1'!$Q$9


Here is my macro so far:

Sub cleanup()
'
' cleanup Macro
'
' Keyboard Shortcut: Ctrl+q
'
' select and copy column C i.e. the reference formulas
Columns("C:C").Select
Selection.Copy
'
' paste col C as Values in column D in the same order, do not delete col C
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'
' sort all by the formula in col E to move all #N/A (i.e. data in
col A
' not existent in sheet 1) to the bottom of the sheet so the Do
While...Loop
' below runs correctly
Columns("A:E").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlDescending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

' starting at row 2 col D, loop until there is a blank row in col D
x = 2
Do While Cells(x, 4).Value < ""
'
' using the reference in each row in col D of sheet 2, go to that
' reference (in sheet 1) and delete the cell contents
Application.Goto reference:="'Sheet 1'!R5447C17"
Selection.ClearContents
'
x = x + 1
Loop

End Sub



--
Thank you in advance for your assistance.
James

Instead of goto just use range

RowCount = 2
Do While Cells(RowCount, "D").Value < ""
'
' using the reference in each row in col D of sheet 2, go to that
' reference (in sheet 1) and delete the cell contents
'
Range(Cells(RowCount,"D")).ClearContents
RowCount = Rowcount + 1
Loop


"James" wrote:

I have most of a macro but am having trouble with the goto command. I want to
use a specific reference in sheet 2 (it is the result of a formula that has
been copied as text to an adjacent column). The specific reference points to
a cell in sheet 1 that I am going to delete.

It is the "Application.Goto reference" line in the Do While...Loop I am
having trouble with - unless someone can think of a better way of getting the
result

How can I use the reference in sheet 2 in my macro without having to
actually type the text? Because typing it in the macro will "fix it" and make
it unchangeable which therefore will not felxible enough to use the
references below it.

Here is a small sample of the list of references I am trying to use in the
macro:
First row is the heading of the list and they are all in Column D of sheet 2:

Current Mail List
'Sheet 1'!$Q$5447
'Sheet 1'!$Q$12
'Sheet 1'!$Q$4
'Sheet 1'!$Q$16
'Sheet 1'!$Q$9


Here is my macro so far:

Sub cleanup()
'
' cleanup Macro
'
' Keyboard Shortcut: Ctrl+q
'
' select and copy column C i.e. the reference formulas
Columns("C:C").Select
Selection.Copy
'
' paste col C as Values in column D in the same order, do not delete col C
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'
' sort all by the formula in col E to move all #N/A (i.e. data in
col A
' not existent in sheet 1) to the bottom of the sheet so the Do
While...Loop
' below runs correctly
Columns("A:E").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlDescending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

' starting at row 2 col D, loop until there is a blank row in col D
x = 2
Do While Cells(x, 4).Value < ""
'
' using the reference in each row in col D of sheet 2, go to that
' reference (in sheet 1) and delete the cell contents
Application.Goto reference:="'Sheet 1'!R5447C17"
Selection.ClearContents
'
x = x + 1
Loop

End Sub



--
Thank you in advance for your assistance.
James

thank you Joel - you are a master. Great solution - appreciate your response

Cheers
--
Thank you in advance for your assistance.
James


"Joel" wrote:

Instead of goto just use range

RowCount = 2
Do While Cells(RowCount, "D").Value < ""
'
' using the reference in each row in col D of sheet 2, go to that
' reference (in sheet 1) and delete the cell contents
'
Range(Cells(RowCount,"D")).ClearContents
RowCount = Rowcount + 1
Loop


"James" wrote:

I have most of a macro but am having trouble with the goto command. I want to
use a specific reference in sheet 2 (it is the result of a formula that has
been copied as text to an adjacent column). The specific reference points to
a cell in sheet 1 that I am going to delete.

It is the "Application.Goto reference" line in the Do While...Loop I am
having trouble with - unless someone can think of a better way of getting the
result

How can I use the reference in sheet 2 in my macro without having to
actually type the text? Because typing it in the macro will "fix it" and make
it unchangeable which therefore will not felxible enough to use the
references below it.

Here is a small sample of the list of references I am trying to use in the
macro:
First row is the heading of the list and they are all in Column D of sheet 2:

Current Mail List
'Sheet 1'!$Q$5447
'Sheet 1'!$Q$12
'Sheet 1'!$Q$4
'Sheet 1'!$Q$16
'Sheet 1'!$Q$9


Here is my macro so far:

Sub cleanup()
'
' cleanup Macro
'
' Keyboard Shortcut: Ctrl+q
'
' select and copy column C i.e. the reference formulas
Columns("C:C").Select
Selection.Copy
'
' paste col C as Values in column D in the same order, do not delete col C
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'
' sort all by the formula in col E to move all #N/A (i.e. data in
col A
' not existent in sheet 1) to the bottom of the sheet so the Do
While...Loop
' below runs correctly
Columns("A:E").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlDescending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

' starting at row 2 col D, loop until there is a blank row in col D
x = 2
Do While Cells(x, 4).Value < ""
'
' using the reference in each row in col D of sheet 2, go to that
' reference (in sheet 1) and delete the cell contents
Application.Goto reference:="'Sheet 1'!R5447C17"
Selection.ClearContents
'
x = x + 1
Loop

End Sub



--
Thank you in advance for your assistance.
James