Search : in
By :

Excel Macro Help Please

Last answer on Nov 3, 2009 2:29:39 pm GMT mathisjr, on Jun 20, 2008 1:43:26 am BST 
 Report this message to moderators

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

Best answers for « Excel Macro Help Please » in :
Excel – Macro to detect and hide blank rows Show Excel – Macro to detect and hide blank rows Issue Solution Note Issue I want a macro to unhide about 20 blank rows copy values into the top row then hide the remaining rows (some cells have fill though) then the next time it will...
Excel – Macro for copy & paste selected range ShowExcel – Macro for copy & paste selected range Issue Solution Note Issue I have a summary of data by month in one sheet and the raw data in another sheet. Instead of creating multiple worksheets for my raw data for each month, I want...
[Excel] – Running Macro Automatically Show[Excel] – Running Macro Automatically Issue Solution Issue I have a macro in excel which needs to be run twice a day and I don’t even want to open the excel sheet. How can I make this process automatic? If I can convert the macro...
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL ¯+ ;¯ in the chosen cell. To insert current time, press CTRL¯+...
Download Super Macro ShowSuper macro is a free software which allows to create macro under Windows in order to activate diverse automatic actions. Apart being free, this software is easy to use and requires no knowledge in programming. You just simply click buttons, then...

1

Ivan-hoe, on Jun 23, 2008 1:38:03 pm BST
  • +8

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

23

Shiva, on Sep 4, 2008 8:17:14 am BST
  • +1

Dear Ivan,

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

Regards
Shivakumar

Reply to Shiva

26

shandor, on Sep 29, 2008 6:48:42 pm BST
  • +1

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.

Reply to shandor

27

Ivan-hoe, on Sep 30, 2008 11:07:24 am BST
  • +2

Hello Shandor,

Range("AL2:AL" & FinalRow).Select

Ivan

Reply to Ivan-hoe

28

Shandor, on Oct 1, 2008 6:36:33 pm BST
  • +6

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.

Reply to Shandor

35

mperre, on Jul 20, 2009 5:06:47 am BST

Haha ... Excel expert!

Reply to mperre

2

mathisjr, on Jun 23, 2008 1:57:11 pm BST
  • +1

Ivan,

That was perfect!

Thank you very much!

Joe

Reply to mathisjr

3

Ivan-hoe, on Jun 23, 2008 3:17:41 pm BST
  • +1

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

Reply to Ivan-hoe

4

B, on Jul 7, 2008 4:19:43 pm BST
  • +2

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

Ivan-hoe, on Jul 10, 2008 3:06:28 pm BST
  • +1

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

B, on Jul 14, 2008 1:38:21 pm BST

Thanks!!! That works perfectly

Reply to B

9

, on Jul 14, 2008 1:40:41 pm BST

Thanks!!!

Reply to

37

Mac, on Sep 9, 2009 1:19:34 pm BST

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.

Reply to Mac

6

ns, on Jul 11, 2008 5:22:23 pm BST
  • +1

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

Ivan-hoe, on Jul 12, 2008 4:24:24 pm BST
  • +1

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

B, on Jul 17, 2008 3:08:35 am BST
  • +1

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

jembuoy, on Jul 23, 2008 3:57:05 am BST

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

Ivan-hoe, on Jul 23, 2008 6:30:16 am BST
  • +2

Hello Jembuoy,
please create a new thread to ask your question (by clicking on "Ask your question")
thank you

Reply to Ivan-hoe

18

jembuoy, on Jul 23, 2008 6:51:24 am BST

Sure thing!

jembuoy

Reply to jembuoy

19

dany, on Jul 29, 2008 2:08:37 pm BST

Hi jembuoy

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

Reply to dany

11

Ivan-hoe, on Jul 17, 2008 8:26:20 am BST
  • +1

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

Kyle, on Jul 20, 2008 11:36:21 pm BST

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

Ivan-hoe, on Jul 21, 2008 5:52:16 am BST

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

Savage, on Jul 22, 2008 9:16:08 pm BST

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

Ivan-hoe, on Jul 23, 2008 6:31:09 am BST

Hello savage,
please create a new thread to ask your question (by clicking on "Ask your question")
thank you

Reply to Ivan-hoe

38

rvd, on Sep 10, 2009 4:48:46 pm BST

Hi,

I have a similar problem in Excel VBA.
I have an entry with daily variable data, so today I have 1000 entries and 1200 the next day. With most of this input data I do some calculations. What I need to do is make a macro that copies these calculation formulas (AE5:FB5) for the new 200 entries.

I got to this script below, I place the cursor on line 1001, column AE, but I have to move the active cell down after a copy. I could also have copied the (AE:FB from the last line) because I already have 1000 entries and formulas. Maybe theres a smatter way to do it, but I'm a begginer in Excel macros.

Thanks for your help

Sub copy()

Dim Bottom As Long
Dim FinalRow As Long
Dim temp As Long


FinalRow = Range("A65536").End(xlUp).Row
CurrentRow = ActiveCell.Row
MsgBox FinalRow & " " & CurrentRow

Range("AE5:fb5").copy
For temp = CurrentRow To FinalRow
ActiveCell.PasteSpecial
ActiveCell.Select = ActiveCell.Row + 1 <--------- WRONG

MsgBox ActiveCell.Rows
Next temp

end sub

Reply to rvd

20

jinta, on Aug 11, 2008 6:43:58 am BST
  • +1

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

aquarelle, on Aug 11, 2008 9:30:11 am BST

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

vel4u, on Aug 19, 2008 12:59:12 pm BST
  • +2

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

25

engineer333, on Sep 4, 2008 8:47:10 pm BST

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.

Reply to engineer333

29

hii, on Oct 27, 2008 10:54:29 am GMT
  • +1

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

Reply to hii

30

lokesh_anand, on Dec 19, 2008 11:39:51 am GMT

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

Reply to lokesh_anand

31

Daneel, on Mar 9, 2009 9:26:38 am GMT

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

Reply to Daneel

32

cameracrazeee, on Mar 17, 2009 8:56:05 pm GMT
  • +2

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.

Reply to cameracrazeee

33

Sainath, on Jun 15, 2009 4:09:25 pm BST

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

Reply to Sainath

34

Pradeep, on Jun 29, 2009 7:15:38 am BST

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

Reply to Pradeep