Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run Time Error '1004' | Excel Worksheet Functions | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Run time error 1004 General ODCB Error | Excel Programming | |||
Application Run Time Error 1004 and Stack Error | Excel Programming | |||
Run time error '1004' | Excel Programming |