Application or object defined error
in [Programming]
|
From: Raj on 26 Mar 2010 02:23 Hi, When I run the following code, I am getting the Application or object defined error. The second line is a single line in my code. The VBE highlights the portion after "Then" in the second line. Sub Cleancolumn1() For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 14).End(xlUp).Row If Left(Cells(i, 14), 1) = "'" Then ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), Len(Cells(i, 14)) - 1) Next i End Sub What is going wrong? Thanks in advance for the help. Regards, Raj
From: ozgrid.com on 26 Mar 2010 02:35 Hi Raj Do you mean? Sub Cleancolumn1() With ThisWorkbook.Worksheets("Sheet1") For i = 1 To .Cells(Rows.Count, 14).End(xlUp).Row If Left(.Cells(i, 14), 1) = "'" Then .Cells(i, 14) = Right(.Cells(i, 14), Len(.Cells(i, 14)) - 1) End If Next i End With End Sub -- Regards Dave Hawley www.ozgrid.com "Raj" <rspai9(a)gmail.com> wrote in message news:9aab4b9b-9698-4667-84cf-6dda14d15489(a)g1g2000pre.googlegroups.com... > Hi, > > When I run the following code, I am getting the Application or object > defined error. The second line is a single line in my code. The VBE > highlights the portion after "Then" in the second line. > > Sub Cleancolumn1() > For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, > 14).End(xlUp).Row > If Left(Cells(i, 14), 1) = "'" Then > ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), > Len(Cells(i, 14)) - 1) > Next i > End Sub > > What is going wrong? > > Thanks in advance for the help. > > Regards, > Raj
From: Raj on 26 Mar 2010 04:05 Yes. But the error again occurred again. This time the line above the End If was highlighted in yellow with the same error code displayed. Regards, Raj On Mar 26, 11:35 am, "ozgrid.com" <d...(a)ozgrid.com> wrote: > Hi Raj > > Do you mean? > > Sub Cleancolumn1() > With ThisWorkbook.Worksheets("Sheet1") > For i = 1 To .Cells(Rows.Count, 14).End(xlUp).Row > If Left(.Cells(i, 14), 1) = "'" Then > .Cells(i, 14) = Right(.Cells(i, 14), Len(.Cells(i, 14)) - 1) > End If > Next i > End With > End Sub > > -- > Regards > Dave Hawleywww.ozgrid.com"Raj" <rsp...(a)gmail.com> wrote in message > > news:9aab4b9b-9698-4667-84cf-6dda14d15489(a)g1g2000pre.googlegroups.com... > > > Hi, > > > When I run the following code, I am getting the Application or object > > defined error. The second line is a single line in my code. The VBE > > highlights the portion after "Then" in the second line. > > > Sub Cleancolumn1() > > For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, > > 14).End(xlUp).Row > > If Left(Cells(i, 14), 1) = "'" Then > > ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), > > Len(Cells(i, 14)) - 1) > > Next i > > End Sub > > > What is going wrong? > > > Thanks in advance for the help. > > > Regards, > > Raj
From: ozgrid.com on 26 Mar 2010 04:25 Do you have a "Sheet1" tab name in "Thisworkbook"? -- Regards Dave Hawley www.ozgrid.com "Raj" <rspai9(a)gmail.com> wrote in message news:12a52095-e99c-4f7b-a89c-d2adf2717a54(a)19g2000yqu.googlegroups.com... Yes. But the error again occurred again. This time the line above the End If was highlighted in yellow with the same error code displayed. Regards, Raj On Mar 26, 11:35 am, "ozgrid.com" <d...(a)ozgrid.com> wrote: > Hi Raj > > Do you mean? > > Sub Cleancolumn1() > With ThisWorkbook.Worksheets("Sheet1") > For i = 1 To .Cells(Rows.Count, 14).End(xlUp).Row > If Left(.Cells(i, 14), 1) = "'" Then > .Cells(i, 14) = Right(.Cells(i, 14), Len(.Cells(i, 14)) - 1) > End If > Next i > End With > End Sub > > -- > Regards > Dave Hawleywww.ozgrid.com"Raj" <rsp...(a)gmail.com> wrote in message > > news:9aab4b9b-9698-4667-84cf-6dda14d15489(a)g1g2000pre.googlegroups.com... > > > Hi, > > > When I run the following code, I am getting the Application or object > > defined error. The second line is a single line in my code. The VBE > > highlights the portion after "Then" in the second line. > > > Sub Cleancolumn1() > > For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, > > 14).End(xlUp).Row > > If Left(Cells(i, 14), 1) = "'" Then > > ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), > > Len(Cells(i, 14)) - 1) > > Next i > > End Sub > > > What is going wrong? > > > Thanks in advance for the help. > > > Regards, > > Raj
From: Raj on 26 Mar 2010 06:49
Thanks, Dave, for the quick response. I do have a Sheet1 in the workbook. To help trouble-shoot the problem I rewrote the code as follows: Sub cleancolumn3() Dim ws As Worksheet Dim rsplen As Long Dim rspstring As String Set ws = ThisWorkbook.Worksheets("Sheet1") For i = 1 To ws.Cells(Rows.Count, 14).End(xlUp).Row If Left(ws.Cells(i, 14), 1) = "'" Then rsplen = Len(ws.Cells(i, 14)) - 1: rspstring = Right(ws.Cells(i, 14), rsplen): ws.Cells(i, 14) = rspstring Next i End Sub The last statement in the If line viz. "ws.Cells(i,14) = rspstring" is highlighted when I debug the Application Defined or Object Defined error. The problem seems to be assigning the string to the cell value. Any other way to do this? Regards, Raj On Mar 26, 1:25 pm, "ozgrid.com" <d...(a)ozgrid.com> wrote: > Do you have a "Sheet1" tab name in "Thisworkbook"? > > -- > Regards > Dave Hawleywww.ozgrid.com"Raj" <rsp...(a)gmail.com> wrote in message > > news:12a52095-e99c-4f7b-a89c-d2adf2717a54(a)19g2000yqu.googlegroups.com... > Yes. But the error again occurred again. This time the line above the > End If was highlighted in yellow with the same error code displayed. > > Regards, > Raj > > On Mar 26, 11:35 am, "ozgrid.com" <d...(a)ozgrid.com> wrote: > > > Hi Raj > > > Do you mean? > > > Sub Cleancolumn1() > > With ThisWorkbook.Worksheets("Sheet1") > > For i = 1 To .Cells(Rows.Count, 14).End(xlUp).Row > > If Left(.Cells(i, 14), 1) = "'" Then > > .Cells(i, 14) = Right(.Cells(i, 14), Len(.Cells(i, 14)) - 1) > > End If > > Next i > > End With > > End Sub > > > -- > > Regards > > Dave Hawleywww.ozgrid.com"Raj" <rsp...(a)gmail.com> wrote in message > > >news:9aab4b9b-9698-4667-84cf-6dda14d15489(a)g1g2000pre.googlegroups.com... > > > > Hi, > > > > When I run the following code, I am getting the Application or object > > > defined error. The second line is a single line in my code. The VBE > > > highlights the portion after "Then" in the second line. > > > > Sub Cleancolumn1() > > > For i = 1 To ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, > > > 14).End(xlUp).Row > > > If Left(Cells(i, 14), 1) = "'" Then > > > ThisWorkbook.Worksheets("Sheet1").Cells(i, 14) = Right(Cells(i, 14), > > > Len(Cells(i, 14)) - 1) > > > Next i > > > End Sub > > > > What is going wrong? > > > > Thanks in advance for the help. > > > > Regards, > > > Raj |