Referencing a row copied over in the macro

[wafs]

I copy over a piece of information in a macro and need to reference that cell within the same macro. Can this be done?? I've tried several things and they all start looking at where the macro left off. I need the macro to look at first cell (A1) instead of the last row.

Here is a small porition of the code.

Dim cellstart As Integer 'starting point of informaiton (row)
Dim commentstart As Integer 'starting point for comments (row)
Dim p As Integer
Dim c As Integer 'for comments
Dim h As Integer 'for comments
Dim x As Integer
Dim r As String 'counting variable for comments
Dim n As String 'counting variable
Dim TestRange As String 'Test String
Dim TestRange2 As String 'Test String
Dim TestRange3 As String 'Test String
Dim TestRange4 As String
Dim TestRange5 As String
Dim TestRange6 As String 'Test String for notes
Dim TestRange7 As String 'Test String for notes
Dim TestRange8 As String 'Test String for notes
Dim TestRange9 As String 'test string for notes
Dim m As String
Dim y As String 'for comments
Dim t As Integer 'for comments
Dim sh As String 'source sheet
Dim destsh As String 'dest sheet
Dim notes As String 'source sheet for comments
Dim reportlocation As String
Dim reportlocation2 As String
Dim header As String 'header you want to run Holds header
Dim output(1 To 8) As String

cellstart = 3 'Starting point of source sheet
commentsstart = 2 'starting point for comments increase this number only if you need more comment space
partstart = 3 'starting point of dest sheet for comments
p = 0
c = 0
header = InputBox("Which header do you want to run?", "header") 'input the requested header

TestRange = header
sh = "Daily" 'Work sheet where the header information is kept
destsh = header ' desination sheet for header
notes = "Notes" 'work sheet for notes

n = cellstart
For x = 1 To 1000 'increase this number only if daily sheet has more than 1000 parts
TestRange2 = "B" + n 'column where to match header request
TestRange3 = "A" + n 'part number
TestRange4 = "AF" + n 'over 60 days
TestRange5 = "AE" + n 'furture 6

If UCase(Trim(TestRange)) = UCase(Trim(sheets(sh).Range(TestRange2))) And UCase(Trim(sheets(sh).Range(TestRange4) < 61)) Then
p = p + 1
m = p + 3 'Tells where to start importing information on dest sheet
reportlocation = "A" + m 'starting point of output file
sheets(sh).Range(TestRange3).Copy sheets(destsh).Range(reportlocation)
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
TestRange4 = "AF" + n
TestRange5 = "AE" + n
Next

'Move Comments
sheets(destsh).Range("A104:B204").Select
Selection.ClearContents

r = commentsstart
t = partstart

output(1) = "A" + r 'part numbers
output(2) = sheets(notes).Range(output(1)).Value
TestRange8 = output(2)
output(3) = "B" + r 'header
output(4) = sheets(notes).Range(output(3)).Value
output(5) = "C" + r 'comments
output(6) = sheets(notes).Range(output(5)).Text
TestRange7 = output(6)
'------------------ TestRange6 = sheets(destsh).Range(reportlocation) need to get this to read column A row 4 on dest sheet
'------------------ TestRange6 = h + t
For x = 1 To 4000
If UCase(Trim(TestRange)) = UCase(Trim(sheets(notes).Range(output(1)).Value)) And UCase(Trim(sheets(notes).Range(output(5)).Text)) <> "" Then
c = c + 1
y = c + 103 'Tells where to start importing comments
reportlocation = "A" + y 'Starting row of comments
sheets(destsh).Range(reportlocation) = TestRange8
reportlocation = "B" + y
sheets(destsh).Range(reportlocation) = TestRange7
r = r + 1
output(3) = "B" + r
output(5) = "C" + r 'Testrang7
output(1) = "A" + r 'TestRange8
Else
r = r + 1
output(3) = "B" + r
output(5) = "C" + r
output(1) = "A" + r
End If
Next

End Sub

 

[Gavona]

I need the macro to look at first cell (A1) instead of the last row."
Range("a1") will do that.
That probably misses the point! Could you not store the range in a variable ready so you can reliably go back to it?

"I copy over a piece of information in a macro"
Which line in your code identifies where the info is copied to?

" need to reference that cell within the same macro"
On which line do you need the reference?



Gavin

 

[wafs]

I'm not sure. What I'm doing is coping from one sheet and inputting into cell (A1:A100) by requirement. What I need is then to look at A1 and then look at the notes sheet and only copy the number in A1 down to A104 and the comments from the notes sheet. If there are no comments, then I don't want anything done. The rows with ***** show where the information is copied to.

r = commentsstart
t = partstart

output(1) = "A" + r 'part numbers
output(2) = sheets(notes).Range(output(1)).Value
TestRange8 = output(2)
output(3) = "B" + r 'header
output(4) = sheets(notes).Range(output(3)).Value
output(5) = "C" + r 'comments
output(6) = sheets(notes).Range(output(5)).Text
TestRange7 = output(6)

For x = 1 To 4000
If UCase(Trim(TestRange)) = UCase(Trim(sheets(notes).Range(output(1)).Value)) And UCase(Trim(sheets(notes).Range(output(5)).Text)) <> "" Then
c = c + 1
******** y = c + 103 'Tells where to start importing comments
*********** reportlocation = "A" + y 'Starting row of comments
************ sheets(destsh).Range(reportlocation) = TestRange8
reportlocation = "B" + y
sheets(destsh).Range(reportlocation) = TestRange7
r = r + 1
output(3) = "B" + r
output(5) = "C" + r 'Testrang7
output(1) = "A" + r 'TestRange8
Else
r = r + 1
output(3) = "B" + r
output(5) = "C" + r
output(1) = "A" + r
End If
Next