rss
Search : in
By : Relevance Date Username
Statut : Solved

Excel Macro Help Please

mathisjr, on Friday June 20, 2008 01:43:26 AM
Hello Everyone,

I am sking for some help with an Excel macro please.

I have a 1,900 hundred static HTML pages that I have converted to text for the purpose of importing to a DB via an Excel CSV file. I have everything sitting in an excel file now with 1,900 hundred rows of nearly identical data (basically label, data, label, data, etc.) with the differences being some could have 5 entries in a row some could have 50. In about half the rows is a label "originator" with a name in the next adjoining cell, not all are in the same colum. I need to go through the file and move the label "originator" and its adjacent "name" to a defined column in my spreadsheet (i've chose 60) so that I have all of the "originator" entrise in the same column. My code works except when I hit a row that has no "originator" I then get "Run-Time error'91': Object variable or With block variable not set"

Could someone please help me to figure out how to skip rows without "originator"?

Thanks,

Joe

Here is my code:

Sub Macro1()
'
' Macro1 Macro
'
Dim r As Integer
Dim c As Integer
Dim x As Integer

' 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

   Selection.Find(What:="originator", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    ActiveCell.Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Cut
    Range("A1").Offset(x, c).Select
    ActiveSheet.Paste
    
 Next
End Sub
Configuration: Windows XP
Internet Explorer 7.0
Reply to mathisjr  Report this message to moderators Go to last message

1


  • This message seems useful, vote!
  • Report this message to moderators
Ivan-hoe, on Monday June 23, 2008 01:38:03 PM
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
Reply to Ivan-hoe

2


  • This message seems useful, vote!
  • Report this message to moderators
mathisjr, on Monday June 23, 2008 01:57:11 PM
Ivan,

That was perfect!

Thank you very much!

Joe
Reply to mathisjr

3


  • This message seems useful, vote!
  • Report this message to moderators
Ivan-hoe, on Monday June 23, 2008 03:17:41 PM
You're welcome, that was a piece of cake.
Ivan
Reply to Ivan-hoe

4


  • This message seems useful, vote!
  • Report this message to moderators
B, on Monday July 7, 2008 04:19:43 PM
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!!!
Reply to B

5


  • This message seems useful, vote!
  • Report this message to moderators
Ivan-hoe, on Thursday July 10, 2008 03:06:28 PM
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
Reply to Ivan-hoe

8


  • This message seems useful, vote!
  • Report this message to moderators
B, on Monday July 14, 2008 01:38:21 PM
Thanks!!! That works perfectly
Reply to B

9


  • This message seems useful, vote!
  • Report this message to moderators
, on Monday July 14, 2008 01:40:41 PM
Thanks!!!
Reply to

6


  • This message seems useful, vote!
  • Report this message to moderators
ns, on Friday July 11, 2008 05:22:23 PM
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!
Reply to ns

7


  • This message seems useful, vote!
  • Report this message to moderators
Ivan-hoe, on Saturday July 12, 2008 04:24:24 PM
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
Reply to Ivan-hoe

10


  • This message seems useful, vote!
  • Report this message to moderators
B, on Thursday July 17, 2008 03:08:35 AM
Hi Ivan et. al,

I have one more question for the excel macro I am programming.

I would like to copy data in Sheet 1, cells L3 and X3 and paste into a single cell (##) in Sheet 2
In order to do this, I would like my macro to search Sheet 2 for the date in Sheet 1, cell F3, and paste the contents of L3 and X3 (sheet 1) into the next cell in the same row of Sheet 2 where the date is found (##).
If cell (##) has data stored in it, I would like the macro to paste into the next available cell in the same row.

Questions:
How do you copy data from two cells and paste into one cell?
How do you find a date in a new sheet given a date input from a different sheet?
How do I offset where I paste if the cell I am trying to paste into has data?

Thanks in advance for your help!

-Brett
Reply to B

15


  • This message seems useful, vote!
  • Report this message to moderators
jembuoy, on Wednesday July 23, 2008 03:57:05 AM
Hi Ivan,

I have an almost similar problem:

I have this in my data let's say on column A:

John Doe
1234 Good St
ATHERTON 94027
650/123-1234
Fax: 650/123-1235
Email: johndoe@gmail.com
web: www.johndoe.com

Jane Doe
1234 Bad St
ATHERTON 94027
650/123-1236
Email: johndoe@gmail.com
web: www.johndoe.com

Then I want them to be transferred/modified horizontally on the same worksheet but on different columns with headers:

Name Street City and ZC Phone Number Fax email web

How do I do it with macros, considering that every bundle of data per person has different row counts (some have phone number some dont) and they are all separated by a row (in excel).

I hope you can help me please.

Jeremy
Reply to jembuoy

16


  • 1
    This message seems useful, vote!
  • Ce message ne vous semble pas utile, votez !
  • Report this message to moderators
Ivan-hoe, on Wednesday July 23, 2008 06:30:16 AM
Hello Jembuoy,
please create a new thread to ask your question (by clicking on "Ask your question")
thank you
Reply to Ivan-hoe

18


  • This message seems useful, vote!
  • Report this message to moderators
jembuoy, on Wednesday July 23, 2008 06:51:24 AM
sure thing!

jembuoy
Reply to jembuoy

19


  • This message seems useful, vote!
  • Report this message to moderators
dany, on Tuesday July 29, 2008 02:08:37 PM
Hi jembuoy

I have the same problem.Please help me by providing the sol.
Reply to dany

11


  • This message seems useful, vote!
  • Report this message to moderators
Ivan-hoe, on Thursday July 17, 2008 08:26:20 AM
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
Reply to Ivan-hoe

12


  • This message seems useful, vote!
  • Report this message to moderators
Kyle, on Sunday July 20, 2008 11:36:21 PM
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
Reply to Kyle

13


  • This message seems useful, vote!
  • Report this message to moderators
Ivan-hoe, on Monday July 21, 2008 05:52:16 AM
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
Reply to Ivan-hoe

14


  • This message seems useful, vote!
  • Report this message to moderators
Savage, on Tuesday July 22, 2008 09:16:08 PM
Ivan,
I have a macro question: Column A has either "active" or blank cells, Column B has "Inactive" or blank cells next to the actives in column A. I want to move all "active" cells from column A to B and leave the Inactives untouched.
Reply to Savage

17


  • This message seems useful, vote!
  • Report this message to moderators
Ivan-hoe, on Wednesday July 23, 2008 06:31:09 AM
Hello savage,
please create a new thread to ask your question (by clicking on "Ask your question")
thank you
Reply to Ivan-hoe

20


  • This message seems useful, vote!
  • Report this message to moderators
jinta, on Monday August 11, 2008 06:43:58 AM
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
Reply to jinta

21


  • This message seems useful, vote!
  • Report this message to moderators
aquarelle, on Monday August 11, 2008 09:30:11 AM
Hello,
If you need help, it would be great to create you own thread for your question. It allows a best following of your problem which is not exactly the same as mathisjr. It also allows to other people who read the thread a best understanding.
Thanks.
Best regards "Pour trouver une solution � ses probl�mes, il faut s'en donner la peine."
Reply to aquarelle

22


  • This message seems useful, vote!
  • Report this message to moderators
 vel4u, on Tuesday August 19, 2008 12:59:12 PM
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
Reply to vel4u
Tips & Tricks relevant found in the knowledge base
21/01 11h11How do I find Word's keyboard shortcuts?Word
More tricks on « Excel Macro Help Please »
Software found in the relevant downloads
Download  Excel Viewer 2007 Excel Viewer - With Excel Viewer, you can open, view, and print Excel workbooks, even if you don't have Excel installed. You can also copy...Category: Office suite
License: Freeware/gratuit
More freeware about « Excel Macro Help Please »