Probably missing something simple in loop macro...Please help

[Guest]

I am hoping someone can tell me what I have missed in the macro. I want it to loop through each row in Col D and if it is not blank, copy and paste special Cols A & B to remove their formulas. The macro changes the Cols A & B in the first row and then saves the sheet and stops. I am very new to this so it is probably an easy fix
Thank

Range("D11").Selec
Selection.AutoFilte
ActiveCell.Offset(1, 0).Selec
Dim R As Objec
For Each R In Range("D11"
If R.Value > ("") The
Selection.End(xlToLeft).Selec
ActiveCell.Offset(0, -1).Range("1:1").Selec
Selection.Cop
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
End I
Next
Application.CutCopyMode = Fals
Range("A11").Selec
Selection.AutoFilte
ActiveWorkbook.Sav
End Sub

 

[Don Guillett]

I can't quite figure out what you want but this should clear col a & b of
the row with something in col d

sub clearAandB()
x=cells(rows.count,"d").end(xlup).row
for each c in range("d1:d"&x)
if c<> "" then cells(c.row,"a").resize(1,2).clearcontents
next
end sub
--
Don Guillett
SalesAid Software
(e-mail address removed)

I am hoping someone can tell me what I have missed in the macro. I want

it to loop through each row in Col D and if it is not blank, copy and paste
special Cols A & B to remove their formulas. The macro changes the Cols A &
B in the first row and then saves the sheet and stops. I am very new to
this so it is probably an easy fix.

 

[Bill Renaud]

Try the following routine. Be sure to set the Const at the top of the
routine to the row number that you want to start converting formulas to
values (I assumed that you have a list, therefore you may not want to do
anything on row 1, since this is the header row).

Public Sub PasteSpecialColAandB()
'Set constant below to the row you want to start on.
Const StartRow As Long = 2

Dim wb As Workbook
Dim ws As Worksheet
Dim lngRow As Long 'Used by For to index through rows of data.
Dim lngNumRows As Long 'Total count of rows of data.

On Error GoTo ErrPasteSpecialColAandB

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

'Get the number of rows of data on the worksheet.
lngNumRows = ws.UsedRange.Rows.Count

For lngRow = StartRow To lngNumRows
With ws
.Cells(lngRow, 4).Select 'Select in case of error.
If Not IsEmpty(.Cells(lngRow, 4)) _
Then
With .Cells(lngRow, 1) 'Column $A.
.Formula = .Value
End With
With .Cells(lngRow, 2) 'Column $B.
.Formula = .Value
End With
End If
End With
Next lngRow

ws.Cells(1, 1).Select
wb.Save
MsgBox "Columns $A and $B converted to values." & vbNewLine & _
"Workbook saved.", vbInformation + vbOKOnly, _
"Paste Special Col A and B"
Exit Sub

ErrPasteSpecialColAandB:
MsgBox "Unknown error on this row or" & vbNewLine & _
"active sheet is not a worksheet!", vbCritical + vbOKOnly, _
"Paste Special Col A and B"
End Sub

--
Regards,
Bill

I am hoping someone can tell me what I have missed in the macro. I want

it to loop through each row in Col D and if it is not blank, copy and paste
special Cols A & B to remove their formulas. The macro changes the Cols A &
B in the first row and then saves the sheet and stops. I am very new to
this so it is probably an easy fix.

 

[Bill Renaud]

Just now saw your other 2 posts earlier today. The code that I included in
my previous post simply runs whenever you start it. It does not wait until a
fixed time of the evening when you are not there tending your computer. I
would put your macros in a separate workbook with a toolbar and a button,
rather than in the workbook with the data.

In the future, can you add new information to your original post with a
reply instead of starting a new post? This will make it easier for another
person to gather all of the information needed to understand the question. I
don't know how your newsgroup reader (Microsoft CDO for Windows 2000) works,
but with Outlook Express, I can click on a column to set the "Watch message"
flag. This makes it easy to quickly find the original post to see if any
replies have been posted. Thanks.