Hi,
I'm trying to loop through cells in column A (say rows 1 to 500) and if the
length of contents is 0, clear contents as there will be apostrophes in the
cells. If on the other hand the length of contents is greater than 0, I
want to place the contents from column A in column C of the same row, whilst
removing the contents of column A.
I've tried various pieces of code but run into Debug mode!
Thanks, Rob
If you have two '' in a cell Len will count 1.
What do you do then?
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Rob" <[email protected]> wrote in message news:%23OupoUu%[email protected]...
> Hi,
> I'm trying to loop through cells in column A (say rows 1 to 500) and if the length of contents is 0, clear contents as there will
> be apostrophes in the cells. If on the other hand the length of contents is greater than 0, I want to place the contents from
> column A in column C of the same row, whilst removing the contents of column A.
>
> I've tried various pieces of code but run into Debug mode!
>
> Thanks, Rob
>
Originally Posted by Rob
Hello Rob,
You didn't say if you are using a Form or just a Control Button on the Worksheet.
I 'll give you code for Button to do the task. This code will work in either case.
Paste the following code into the Click Event of your Macro Button.
________________________________________________________________
Private Sub CommandButton1_Click()
Dim Wks As Worksheet
Dim I As Long
Dim Test As Long
Set Wks = Excel.worksheets("Sheet1") 'Use your Worksheet's name
For I = 1 To 500
'Convert Cell contents to a Number
Test = Val(Wks.Cells(1, I).Value)
'Test if Cell(A) is empty
If Test = 0 Then
'If so Clear the Cell
Wks.Cells(1, I).Clear
Else
'Copy Cell(A) contents to Cell(C) on the same Row
Wks.Cells(3, 1).Value = Wks.Cells(1, I).Value
End If
Next I
Hope this Helps,
Leith Ross
Use the Val statement to return the Numeric Value of the Cell. It will convert upto the first non numeric character in the String. The Double Quotes will simply return zero.
Cell A1 = ""
X = Val(Cells(1, 1).Value)
X will be zero
Hope that helps,
Leith
Ron,
Because the apostrophes are set by a previous routine, there will only be
two tests: firstly whether the cell appears empty but has an apostrophe or
whether there is some text.
I'm really interested in moving the text from column A to CO but can't just
copy and paste as there is other contents in column CO.
Thanks, Rob
"Ron de Bruin" <[email protected]> wrote in message
news:%23Go5u5u%[email protected]...
> If you have two '' in a cell Len will count 1.
> What do you do then?
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Rob" <[email protected]> wrote in message
> news:%23OupoUu%[email protected]...
>> Hi,
>> I'm trying to loop through cells in column A (say rows 1 to 500) and if
>> the length of contents is 0, clear contents as there will be apostrophes
>> in the cells. If on the other hand the length of contents is greater
>> than 0, I want to place the contents from column A in column C of the
>> same row, whilst removing the contents of column A.
>>
>> I've tried various pieces of code but run into Debug mode!
>>
>> Thanks, Rob
>>
>
>
Hi Rob
Try this one for the activesheet
But If you have a cell with two '' in it it will also copy the cell to the C column
Sub Example()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 500
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf Len(.Cells(Lrow, "A").Value) = 0 Then
.Cells(Lrow, "A").ClearContents
Else
.Cells(Lrow, "C").Value = .Cells(Lrow, "A").Value
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Rob" <[email protected]> wrote in message news:u3VN7vw%[email protected]...
> Ron,
>
> Because the apostrophes are set by a previous routine, there will only be two tests: firstly whether the cell appears empty but
> has an apostrophe or whether there is some text.
>
> I'm really interested in moving the text from column A to CO but can't just copy and paste as there is other contents in column
> CO.
>
> Thanks, Rob
>
> "Ron de Bruin" <[email protected]> wrote in message news:%23Go5u5u%[email protected]...
>> If you have two '' in a cell Len will count 1.
>> What do you do then?
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Rob" <[email protected]> wrote in message news:%23OupoUu%[email protected]...
>>> Hi,
>>> I'm trying to loop through cells in column A (say rows 1 to 500) and if the length of contents is 0, clear contents as there
>>> will be apostrophes in the cells. If on the other hand the length of contents is greater than 0, I want to place the contents
>>> from column A in column C of the same row, whilst removing the contents of column A.
>>>
>>> I've tried various pieces of code but run into Debug mode!
>>>
>>> Thanks, Rob
>>>
>>
>>
>
>
Ron,
Thanks for this, I'd been trying with OffSet and failing miserably.
Regards, Rob
"Ron de Bruin" <[email protected]> wrote in message
news:e5nfn7w%[email protected]...
> Hi Rob
>
> Try this one for the activesheet
> But If you have a cell with two '' in it it will also copy the cell to the
> C column
>
> Sub Example()
> Dim Lrow As Long
> Dim CalcMode As Long
> Dim StartRow As Long
> Dim EndRow As Long
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> With ActiveSheet
> .DisplayPageBreaks = False
> StartRow = 1
> EndRow = 500
> For Lrow = EndRow To StartRow Step -1
> If IsError(.Cells(Lrow, "A").Value) Then
> 'Do nothing, This avoid a error if there is a error in the
> cell
> ElseIf Len(.Cells(Lrow, "A").Value) = 0 Then
> .Cells(Lrow, "A").ClearContents
> Else
> .Cells(Lrow, "C").Value = .Cells(Lrow, "A").Value
> End If
> Next
> End With
> With Application
> .ScreenUpdating = True
> .Calculation = CalcMode
> End With
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Rob" <[email protected]> wrote in message
> news:u3VN7vw%[email protected]...
>> Ron,
>>
>> Because the apostrophes are set by a previous routine, there will only be
>> two tests: firstly whether the cell appears empty but has an apostrophe
>> or whether there is some text.
>>
>> I'm really interested in moving the text from column A to CO but can't
>> just copy and paste as there is other contents in column CO.
>>
>> Thanks, Rob
>>
>> "Ron de Bruin" <[email protected]> wrote in message
>> news:%23Go5u5u%[email protected]...
>>> If you have two '' in a cell Len will count 1.
>>> What do you do then?
>>>
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>>
>>> "Rob" <[email protected]> wrote in message
>>> news:%23OupoUu%[email protected]...
>>>> Hi,
>>>> I'm trying to loop through cells in column A (say rows 1 to 500) and if
>>>> the length of contents is 0, clear contents as there will be
>>>> apostrophes in the cells. If on the other hand the length of contents
>>>> is greater than 0, I want to place the contents from column A in column
>>>> C of the same row, whilst removing the contents of column A.
>>>>
>>>> I've tried various pieces of code but run into Debug mode!
>>>>
>>>> Thanks, Rob
>>>>
>>>
>>>
>>
>>
>
>
In this example you can use the Offset like this
.Cells(Lrow, "A").Offset(0, 2).Value = .Cells(Lrow, "A").Value
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Rob" <[email protected]> wrote in message news:Oz4J9Lx%[email protected]...
> Ron,
>
> Thanks for this, I'd been trying with OffSet and failing miserably.
>
> Regards, Rob
>
> "Ron de Bruin" <[email protected]> wrote in message news:e5nfn7w%[email protected]...
>> Hi Rob
>>
>> Try this one for the activesheet
>> But If you have a cell with two '' in it it will also copy the cell to the C column
>>
>> Sub Example()
>> Dim Lrow As Long
>> Dim CalcMode As Long
>> Dim StartRow As Long
>> Dim EndRow As Long
>> With Application
>> CalcMode = .Calculation
>> .Calculation = xlCalculationManual
>> .ScreenUpdating = False
>> End With
>>
>> With ActiveSheet
>> .DisplayPageBreaks = False
>> StartRow = 1
>> EndRow = 500
>> For Lrow = EndRow To StartRow Step -1
>> If IsError(.Cells(Lrow, "A").Value) Then
>> 'Do nothing, This avoid a error if there is a error in the cell
>> ElseIf Len(.Cells(Lrow, "A").Value) = 0 Then
>> .Cells(Lrow, "A").ClearContents
>> Else
>> .Cells(Lrow, "C").Value = .Cells(Lrow, "A").Value
>> End If
>> Next
>> End With
>> With Application
>> .ScreenUpdating = True
>> .Calculation = CalcMode
>> End With
>> End Sub
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Rob" <[email protected]> wrote in message news:u3VN7vw%[email protected]...
>>> Ron,
>>>
>>> Because the apostrophes are set by a previous routine, there will only be two tests: firstly whether the cell appears empty but
>>> has an apostrophe or whether there is some text.
>>>
>>> I'm really interested in moving the text from column A to CO but can't just copy and paste as there is other contents in column
>>> CO.
>>>
>>> Thanks, Rob
>>>
>>> "Ron de Bruin" <[email protected]> wrote in message news:%23Go5u5u%[email protected]...
>>>> If you have two '' in a cell Len will count 1.
>>>> What do you do then?
>>>>
>>>>
>>>> --
>>>> Regards Ron de Bruin
>>>> http://www.rondebruin.nl
>>>>
>>>>
>>>>
>>>> "Rob" <[email protected]> wrote in message news:%23OupoUu%[email protected]...
>>>>> Hi,
>>>>> I'm trying to loop through cells in column A (say rows 1 to 500) and if the length of contents is 0, clear contents as there
>>>>> will be apostrophes in the cells. If on the other hand the length of contents is greater than 0, I want to place the contents
>>>>> from column A in column C of the same row, whilst removing the contents of column A.
>>>>>
>>>>> I've tried various pieces of code but run into Debug mode!
>>>>>
>>>>> Thanks, Rob
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
To clear A when the value is moved, add the below line:
Sub Example()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 500
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf Len(.Cells(Lrow, "A").Value) = 0 Then
.Cells(Lrow, "A").ClearContents
Else
.Cells(Lrow, "C").Value = .Cells(Lrow, "A").Value
.Cells(Lrow, "A").ClearContents ' <=== added line
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
--
Regards,
Tom Ogilvy
"Ron de Bruin" <[email protected]> wrote in message
news:e5nfn7w%[email protected]...
> Hi Rob
>
> Try this one for the activesheet
> But If you have a cell with two '' in it it will also copy the cell to the
C column
>
> Sub Example()
> Dim Lrow As Long
> Dim CalcMode As Long
> Dim StartRow As Long
> Dim EndRow As Long
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> With ActiveSheet
> .DisplayPageBreaks = False
> StartRow = 1
> EndRow = 500
> For Lrow = EndRow To StartRow Step -1
> If IsError(.Cells(Lrow, "A").Value) Then
> 'Do nothing, This avoid a error if there is a error in the
cell
> ElseIf Len(.Cells(Lrow, "A").Value) = 0 Then
> .Cells(Lrow, "A").ClearContents
> Else
> .Cells(Lrow, "C").Value = .Cells(Lrow, "A").Value
> End If
> Next
> End With
> With Application
> .ScreenUpdating = True
> .Calculation = CalcMode
> End With
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Rob" <[email protected]> wrote in message
news:u3VN7vw%[email protected]...
> > Ron,
> >
> > Because the apostrophes are set by a previous routine, there will only
be two tests: firstly whether the cell appears empty but
> > has an apostrophe or whether there is some text.
> >
> > I'm really interested in moving the text from column A to CO but can't
just copy and paste as there is other contents in column
> > CO.
> >
> > Thanks, Rob
> >
> > "Ron de Bruin" <[email protected]> wrote in message
news:%23Go5u5u%[email protected]...
> >> If you have two '' in a cell Len will count 1.
> >> What do you do then?
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Rob" <[email protected]> wrote in message
news:%23OupoUu%[email protected]...
> >>> Hi,
> >>> I'm trying to loop through cells in column A (say rows 1 to 500) and
if the length of contents is 0, clear contents as there
> >>> will be apostrophes in the cells. If on the other hand the length of
contents is greater than 0, I want to place the contents
> >>> from column A in column C of the same row, whilst removing the
contents of column A.
> >>>
> >>> I've tried various pieces of code but run into Debug mode!
> >>>
> >>> Thanks, Rob
> >>>
> >>
> >>
> >
> >
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks