Excel Macro Help Please

Hi mathisjr,
if "originator" is not found, the cell determined by the method Find does not exist either. So it cannot be activated.

I modified below the lines immediately after '*** , in order to test if the method Find does find something or not :

Sub Macro1()

Dim r As Integer
Dim c As Integer
Dim x As Integer
'***
Dim MyRange As Range

' Count the number of data rows in the table
totalrows = ActiveSheet.UsedRange.Rows.Count

c = 60

For r = 1 To totalrows
   x = r - 1
   Rows(r).Select
'***
        Set MyRange = Selection.Find(What:="originator", After:=ActiveCell, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'to be deleted
'   Selection.Find(What:="originator", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
 
 '***
 If Not MyRange Is Nothing Then
    Range(MyRange, MyRange.End(xlToRight)).Select
'to be deleted
    'ActiveCell.Select
    'Range(Selection, Selection.End(xlToRight)).Select
    Selection.Cut
    Range("A1").Offset(x, c).Select
    ActiveSheet.Paste
'***
 End If
 Next
End Sub

I hope it helps.
Ivan

Ivan,

That was perfect!

Thank you very much!

Joe

You're welcome, that was a piece of cake.
Ivan

I am trying to write a macro that copies data from a range and pastes it in the next open row in a different sheet.

mycount = Range("A12") + 1
Range("A12") = mycount

Range("C12:Z12").Select
Range("C12:AA12").Select
Selection.Copy
Sheets("3P FX").Select
Application.Run "BLPLinkReset"
Range("A113").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

This formula copies the information from C12-Z12 and pastes it in A113 in sheet 3PFX, but I would like it to paste in cell (A113 + value in A12) where A12 is the counter.

How do I command excel to copy into cell (A113+value in A12)?

Thanks!!!

Hello B,
instead of

Range("A113").Select

you should write

Range("A113").Offset(mycount,0).Select

This moves cell A113 forward mycount rows and 0 columns.
Ivan

Hello,

Can any please help me how do I search for a specific cell with data like "Name" and paste the data after that row into a new column? How do I count the row after "Name" cell like "Name"+1 and then "Name" + 2

such as if cell = "Name"
copy the cell(row) after that
paste into Column N


Thanks!

Hello,
you can use VBA instructions such as

Dim i As Integer
If ActiveCell.Value = "Name" Then
    i = ActiveCell.Row
    Range("N" & i).Value = ActiveCell.Offset(1, 0).Value
End If

or

Cells.Find("Name").Activate
Range("N" & ActiveCell.Row).Value = ActiveCell.Offset(1, 0).Value

Ivan

Good morning,

2-To find a value in a sheet, use the method Find
Dim TheCellImLookingFor as Range
Set TheCellImLookingFor = Sheets(2).Cells.Find (What:=Sheets(1).Range("F3").Value)

3-To find the next available cell in the same row, use .End(xlToRight)
Dim TheCellIWantToPasteIn as Range
If TheCellImLookingFor.Offset(0,1).Value = Empty Then
Set TheCellIWantToPasteIn = TheCellImLookingFor.Offset(0,1)
Else
Set TheCellIWantToPasteIn = TheCellImLookingFor.End(xlToRight).Offset(0,1)
End If

1-To copy data from two cells into one cell, you have to concatenate the contents of the two cells, using &
TheCellIWantToPasteIn.Value = Sheets(1).Range("L3").Value & Sheets(1).Range("X3").Value
If you wish to have the contents of each cell on different lines, use Chr(10) and Chr(13)
TheCellIWantToPasteIn.Value = Sheets(1).Range("L3").Value & Chr(10) & Chr(13) & Sheets(1).Range("X3").Value

Have a nice day.
Ivan

Ivan,

You seem to be really good at programming macros so I have question for you.... I am trying to write a macro that works down a page (down column "A") looking to see if there is anything entered into it. After that it is will copy and paste some other cells to another worksheet. I have everything figured out except how to make it work down the column looking for a value. This is the code I have pretty simple...all i was trying to do to start was copy the first section. The macro will hopefully allow me to type a list of name for golf tournaments once rather than three times.

Sub PrintScorecardsClick()

Sheets("List").Activate
Range("A1").Select
If ActiveCell = "" Then End
If ActiveCell > "0" Then
Worksheets("List").Range("B2:B5").Copy
ActiveSheet.Paste Destination:=Worksheets("Scorecard").Range("L10:L13")
Worksheets("List").Range("B2:B5").Copy
ActiveSheet.Paste Destination:=Worksheets("Scorecard").Range("L27:L30")
Worksheets("List").Range("A2").Copy
ActiveSheet.Paste Destination:=Worksheets("scorecard").Range("L19")
Sheets("Scorecard").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
End If


End Sub

Hello,
if you only need to determine if there is any value entered in column A, use the function CountA

Sheets("List").Activate
If Application.WorksheetFunction.CountA(Columns("A")) = 0 Then
Exit Sub
Else
'instructions
End If

If you absolutely need to work down column A to see if there is anything entered in each cell, then use For ... Next

Dim LastLine As Integer, i As Integer

Sheets("List").Activate
LastLine = Cells(Columns("A").Rows.Count, 1).End(xlUp).Row

For i = 1 To LastLine
If Not Range("A" & i).Value = Empty Then
'instructions
End If
Next i

Ivan

Hi Sir,

I want to copy values in the range A1:I1 to every alternate row.. ie to A3: I3, A5 :I5 .. there are almost 200 rows are there.


Thanks
Jinta

Hi Ivan,

I need a macro to copy the numbers from one cell and paste it in to different rows in another excel sheet.

For example:

If any cell has numbers like, 52131,52152,52515,51525. (note: some time it has space after comma (,))

I want this to be pasted in rows as below (without comma(,)),

52131
52152
52515
51525


Could you please provide me a macro?

Thanks

Vel