application.goto reference using references in the worksheet

J

James

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
 
B

Bob Phillips

Do you have a cell reference in D, like say M17? If so, tis should do it

With Worksheets("Sheet 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
.Cells(.Value).ClearContents
'
x = x + 1
Loop
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
J

James

Thankyou very much Bob - appreciate your response and solution.

Cheers

James


Bob Phillips said:
Do you have a cell reference in D, like say M17? If so, tis should do it

With Worksheets("Sheet 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
.Cells(.Value).ClearContents
'
x = x + 1
Loop
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top