Excel Macro Help Please

Solved/Closed
mathisjr - Jun 19, 2008 at 08:43 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Jul 7, 2010 at 08:02 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

23 replies

Ivan-hoe
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
Jun 23, 2008 at 08:38 AM
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
20
Dear Ivan,

can you just give me your mail Id so that I can send an attachement..

Regards
Shivakumar
0
Im sorry to plague you along with all these other questions, but what I am doing seems so damn similar, and I can't find it anywhere. I am rather new to excel macros (many years programming other things).

I just need to copy and paste a formula into a column for as many rows as exist in the column I am copying from. I know the answer is stupid easy, I just dont knwo how to do it.

here is the code

Sub test()
'
' test Macro
' Macro recorded 9/29/2008 by Robert
'
FinalRow = Range("A65536").End(xlUp).Row

'
Range("AL1").Select
ActiveCell.FormulaR1C1 = "Hours"
Range("AL2").Select
ActiveCell.FormulaR1C1 = "=A2/3600"
Range("AL2").Select
Selection.NumberFormat = "0.00"
Selection.Copy
Range(("AL2"), FinalRow).Select <----------- obviously this line is wrong, i just dont know how to fix it
ActiveSheet.Paste

End Sub

I know that it finds the last row correctly, i just dont know how to select the correct range to paste the formula into.
0
Ivan-hoe
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109 > shandor
Sep 30, 2008 at 06:07 AM
Hello Shandor,
Range("AL2:AL" & FinalRow).Select

Ivan
0
Shandor
Posts
2
Registration date
Sunday September 28, 2008
Status
Member
Last seen
October 1, 2008
> Ivan-hoe
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008

Oct 1, 2008 at 01:36 PM
omg....i didnt think it would be THAT easy.

Thanks! It seems VBA tries to be very adaptable and coming from C it is very confusing to me.
0
Haha ... Excel expert!
0
vel4u
Posts
2
Registration date
Monday August 18, 2008
Status
Member
Last seen
August 19, 2008
10
Aug 19, 2008 at 07:59 AM
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
10
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!!!
6
cameracrazeee
Mar 17, 2009 at 04:56 PM
Hello-
I have a question in workbooks, how would i get information from one sheet to another sheet?
If i type information in one sheet.
how do i save that in another sheet
and then write OVER it in the first sheet
but have that information go on the next row down
and have the second sheet keep records of everything.
6

Didn't find the answer you are looking for?

Ask a question
hiii joe


it is very simple , very easy i have given the code by skipping the originator










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:=""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 SubConfiguration: Windows XP
Internet Explorer 7.0
3
Hi Ivan, I have the same exact problem as Jeremy below, but I didn't see the solution. PLEASE HELP! :)

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
3
Ivan-hoe
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
Jun 23, 2008 at 10:17 AM
You're welcome, that was a piece of cake.
Ivan
0
Ivan-hoe
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
Jul 10, 2008 at 10:06 AM
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
0
Thanks!!! That works perfectly
0
Anonymous User
Jul 14, 2008 at 08:40 AM
Thanks!!!
0
Hi Ivan

I wonder if you can help me.

i have some data that i want sorted by Area (i.e North West, West midlands etc) and then post the data to workbooks of that area. is there a way i can do this using vba? i am a very new to macros and would appreciate the help.
0
Ivan-hoe
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
Jul 12, 2008 at 11:24 AM
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
0
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
0
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
0
Ivan-hoe
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109 > jembuoy
Jul 23, 2008 at 01:30 AM
Hello Jembuoy,
please create a new thread to ask your question (by clicking on "Ask your question")
thank you
0
jembuoy > Ivan-hoe
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008

Jul 23, 2008 at 01:51 AM
sure thing!

jembuoy
0
dany > jembuoy
Jul 29, 2008 at 09:08 AM
Hi jembuoy

I have the same problem.Please help me by providing the sol.
0
Ivan-hoe
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
Jul 17, 2008 at 03:26 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
0
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
0
ok, i don't know if someone can help me out, this is really simple i am sure. i have a sheet with different occupancies for hot water calculation. i am trying to set up a formula that if i enter a value in a column, it will then run through my calculations and return the total to my total row in that specific column. so for instance, in column b, if i enter 1 in for a shower, it goes through, multiplies out and gives me my 30 FU for hot water in L26. i then want the answer from L26 to be in B26. I only want the answer to show up in the corresponding column based on if there is any value in that column. so that C26 through K26 still show 0.

i had a bunch of IF statements, but they were adding them together, which i didn't want. I don't know if i can separate the IF statements to give me the correct value, or if i need something else.

any help would be much appreciated.
0
lokesh_anand
Posts
2
Registration date
Thursday December 18, 2008
Status
Member
Last seen
December 19, 2008

Dec 19, 2008 at 06:39 AM
Hello Everyone,

I am totally new to using macros in excel and so I would really be grateful if I could get some help on what I have to do. Please also excuse me if I am not specific or vague and let me know so I can provide you with more information if I have.

I have a list of data which is in a master list. I have to have a system such that while looking for a specific data, I run the macro and it would prompt for entering the data name or type..it would then take that data from the matser list and paste it in a specific table in a separate worksheet. I understand that it might not be that difficult but would just like some guidance on the direction to take and the set of commands I should be looking to use so that I can read more and try and solve it.

So in summary:
1. I have a master list from which any data on the type of cables for eg..can be found
2. I need to have a system whereby when prompted for the specific type of cable, it would copy and paste it into a different worksheet in a table there.

I would be extremely grateful for any kind of help!

cheers
0
hey there

i have a problem and hope that someone help me.
problem is : creating a unique id from diffrent factors in the above rows in the same column .
i could mix the numbers but there is a 4 digit that shold be work as a counter in the middle of it (and these 4 digits also have diffrent conditions ie. 0000-4999 fo some material and 5001-9999 for something else) and i want it to autogenerate the new code by clicking on the cell .
i hope that i have explained the problem well cuase im not fuent in english.
if you need more info i could email the file .

plz help me
0
Can you please provide me with a macro code where a required inputed value in cell A1 of sheet 1 is searched from data in sheet 2, sheet 3, sheet 4 and sheet 5 and then the entire row corresponding to the searched value in any of the sheets 2, 3, 4, 5 is copy pasted in cell E1 of sheet 1. If the inputed value in cell A1 of sheet 1 is not found in any of the sheets 2, 3, 4, or 5 then cell f1 of sheet 1 will contain the text "Not available in database".
0
Hi Ivan,

I was trying to a get a macro
which can copy and paste based on certain conditions.

I have a sheet with 2 colums, A and B. A contains numerals integers from 0 to 40 and B contains some names. I need to create a 3rd column C which needs to be pasted with column B's data. The condition is that

1) If A is 0, then column C should be blank
2) If A is equal to 1, then C should take the value of in column B which correspons to previous A=0
3) If A is equal to 2, then C should take the value of in column B which correspons to previous A=1..
4) and so on..

I am desperately looking for a help.. Please advice.

here is an munually created example,

A B C
0 500239
1 0450184-002 500239
1 0450184-202 500239
2 801427 0450184-202
1 2660-0273 500239
0 500291
1 0450184-002 500291
2 0450185-003 0450184-002
3 801392 0450185-003
0 500369
1 500023 500369
0
I have few rows like below in single row and I want to select all cells starting with JE* and writing into another workbook.

ENF
ENSTKT
GSSA
HSM2
HZSPROC
IOSAS
IXGLOGR
JESXCF
JES2
JES2MON
JHSCMAIN
JHSLMAIN
JHSPMAIN
JOB
LLA
MBSERVRP
NDMDTF

Please helo me out.
0
I am trying to find any “integer,integer” and remove the “,”.
If this macro finds each “integer,integer” it works, but as soon as it does not find one it stops with (macro run-time error '91'), and will not look for the next number in sequence.

How can I change it to continue through rows that do not have criteria. I can then add each macro sequence until I get to 9,9?

Or can I put a wild card in (“integer,integer”, “integer,integer” does not work ) that will look up 0-9 on both sides of the “,”?

' Macro3 Macro
' Macro recorded 12/27/2009 by kunkleman
'

'
Columns("C:C").Select
Selection.Find(What:="0,0", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0,0", Replacement:="00", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="0,1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0,1", Replacement:="01", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="0,2", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0,2", Replacement:="02", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="0,3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0,3", Replacement:="03", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="0,4", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0,4", Replacement:="04", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="0,5", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Replace What:="0,5", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="0,5", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0,5", Replacement:="05", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="0,6", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0,6", Replacement:="06", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="0,7", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0,7", Replacement:="07", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="0,8", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0,8", Replacement:="08", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="0,9", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0,9", Replacement:="09", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="1,0", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="1,0", Replacement:="10", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Find(What:="1,1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="1,1", Replacement:="11", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub
0
I have 2000 rows of names and numbers that are all in column A. I need to move all of the numbers into column B. They are all shown like this with a comma separating the the name from the number and no spaces before or after the comma - ken roe,9194986429.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jun 12, 2010 at 03:16 PM
Insert a blank column after the column in which you have this data
Select your data column
Go to Data and Choose text to column
Choose delimited and click next
choose "comma" and click finish
0
Ivan,

That was perfect!

Thank you very much!

Joe
-1