Excel Macro Help PleaseSolved/Closed

Question

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

Ivan-hoe · Accepted Answer

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

mathisjr · Answer

Ivan,

That was perfect!

Thank you very much!

Joe

Ivan-hoe · Answer

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

B · Answer

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!!!

Ivan-hoe · Answer

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

ns · Answer

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!

Ivan-hoe · Answer

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

Ivan-hoe · Answer

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

Kyle · Answer

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

Ivan-hoe · Answer

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

jinta · Answer

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

vel4u · Answer

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

engineer333 · Answer

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.

hii · Answer

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

lokesh_anand · Answer

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

Daneel · Answer

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

cameracrazeee · Answer

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.

Sainath · Answer

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".

Pradeep · Answer

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

John Stole · Answer

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.

Angela · Answer

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

Newbe · Answer

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

Wade · Answer

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.

Excel Macro Help Please

23 responses

Similar discussions

Subscribe To Our Newsletter!