Excel Macro Help Please

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

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

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.
Haha ... Excel expert!
Hi mathisjr

I got a problem in Excel sheet that have Macro. Somehow it is working any more could you please send your email address so I'll be able to attache the file zip.

arrowarrow_2001@hotmail.com

Regards,

Labib
What is the meaning of r = Cells(Rows.Count, 1).End(xlUp).Row
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Rows.count gives you number of rows in the active sheet. Depending on version of excel you have this varies. Like before 2007 rows were 65536 and now a lot more

1 is the first column or column A
so cells(rows.count, 1) is same as cells(rows.count, "A') and means the last cell in the column A
.end is bit tricky to explain. It is basically as if you have pressed the control key and using arrow

xlup, is like saying pressing keyup

so
Cells(Rows.Count, 1).End(xlUp)
is like you are the at the last cell of column A and then use ctrl + arrow up to the last used cell or if the column A is totally empty the cell A1.

.row is to see the row property of the cell to see what row it would be

so all in all
Cells(Rows.Count, 1).End(xlUp).Row is saying that in column 1, from the bottom of the column show me the row number where there is a data (in case there is no data, it will show you 1. so one is exception in that it may have data or it may not have data)
Posts
2
Registration date
Monday August 18, 2008
Status
Member
Last seen
August 19, 2008
10
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
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-
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.
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
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
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
You're welcome, that was a piece of cake.
Ivan
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
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
Thanks!!! That works perfectly

Thanks!!!
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.
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
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
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
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
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109 > jembuoy
Hello Jembuoy,
please create a new thread to ask your question (by clicking on "Ask your question")
thank you
>
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008

sure thing!

jembuoy
> jembuoy
Hi jembuoy

I have the same problem.Please help me by providing the sol.
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
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
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.
Posts
2
Registration date
Thursday December 18, 2008
Status
Member
Last seen
December 19, 2008

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
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
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".
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
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.
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
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Ivan,

That was perfect!

Thank you very much!

Joe