run-time error 1004 & comment extraction
 

Within a couple of embedded loops I am trying to extract a comment , process
the comment, and place a value in a separate sheet . I get the following
error
'Run time error 1004'
'Application-defined or object defined error'
Please help
code is below

Sub establish_onoffattr()
Dim i, p As Integer
p = 1
Do While Worksheets("HC").Cells(p, 1) < ""
For i = 1 To 43
Call range_calc(Srange, (p), (i))
' df = Worksheets("HC").Range(Srange).Comment.Text

'assign cell value to a string variable
With Worksheets("HC").Range(Srange).Select ' ERROR OCCURS HERE

'Srange = "A1"
Dim tst As String
tst = ActiveCell.Value
' Further manipulatinos to occur here
End With
Next i
p = p + 1
Loop
Sub range_calc(Astr, row As Integer, col As Long)
'Dim row, col As Integer
Dim l As Long
Dim alpha

FS = col \ 26
SRemainder1 = col Mod 26
If FS = 0 Then
sx = Chr(64 + SRemainder1)
Else
sx = Chr(64 + FS) + Chr(64 + SRemainder1)
End If
l = col
alpha = str(l)
Astr = sx + Mid(str(row), 2)
End Sub

run-time error 1004 & comment extraction
 

Hello,
I think the problem is using the Select method in a With statement.
The With statement sets up a type of shortcut in your code. For example:
With Workbooks(1).Sheets(1).Range("A1")
.Value="Cell A1" 'Same as Workbooks(1).Sheets(1).Range("A1").Value="Cell
A1"
.Select 'Same as Workbooks(1).Sheets(1).Range("A1").Select
End With
It is not necessary to Select a range to use it, unless you want to see it
"selected" when you look at the Worksheet. If you DO need Srange selected,
move .Select to the next line.

Hope this helps.

Shadhi


"GL Fr" wrote:

Within a couple of embedded loops I am trying to extract a comment , process
the comment, and place a value in a separate sheet . I get the following
error
'Run time error 1004'
'Application-defined or object defined error'
Please help
code is below

Sub establish_onoffattr()
Dim i, p As Integer
p = 1
Do While Worksheets("HC").Cells(p, 1) < ""
For i = 1 To 43
Call range_calc(Srange, (p), (i))
' df = Worksheets("HC").Range(Srange).Comment.Text

'assign cell value to a string variable
With Worksheets("HC").Range(Srange).Select ' ERROR OCCURS HERE

'Srange = "A1"
Dim tst As String
tst = ActiveCell.Value
' Further manipulatinos to occur here
End With
Next i
p = p + 1
Loop
Sub range_calc(Astr, row As Integer, col As Long)
'Dim row, col As Integer
Dim l As Long
Dim alpha

FS = col \ 26
SRemainder1 = col Mod 26
If FS = 0 Then
sx = Chr(64 + SRemainder1)
Else
sx = Chr(64 + FS) + Chr(64 + SRemainder1)
End If
l = col
alpha = str(l)
Astr = sx + Mid(str(row), 2)
End Sub

run-time error 1004 & comment extraction
 

Thanks for the reply Shadhi
But I am still having problems

the essence of the problem is
-to capture the comment text from a particular cell(i,j)
-process the comment to extract a particular string
-then write the value to another Cell

I had looked up in the news group and found one potential solution, which
upon trying failed (so I resorted to the news group)

I tried your advice, but I still get the 1004 error


"GL Fr" wrote:

Within a couple of embedded loops I am trying to extract a comment , process
the comment, and place a value in a separate sheet . I get the following
error
'Run time error 1004'
'Application-defined or object defined error'
Please help
code is below

Sub establish_onoffattr()
Dim i, p As Integer
p = 1
Do While Worksheets("HC").Cells(p, 1) < ""
For i = 1 To 43
Call range_calc(Srange, (p), (i))
' df = Worksheets("HC").Range(Srange).Comment.Text

'assign cell value to a string variable
With Worksheets("HC").Range(Srange).Select ' ERROR OCCURS HERE

'Srange = "A1"
Dim tst As String
tst = ActiveCell.Value
' Further manipulatinos to occur here
End With
Next i
p = p + 1
Loop
Sub range_calc(Astr, row As Integer, col As Long)
'Dim row, col As Integer
Dim l As Long
Dim alpha

FS = col \ 26
SRemainder1 = col Mod 26
If FS = 0 Then
sx = Chr(64 + SRemainder1)
Else
sx = Chr(64 + FS) + Chr(64 + SRemainder1)
End If
l = col
alpha = str(l)
Astr = sx + Mid(str(row), 2)
End Sub