Macro error 438--How to edit macro, so can cover more rows

[ronlau123]

Hi, guys. I have the spreadsheet with 2 macros. It includes many rows (more than +5000 rows). This spreadsheet is designed many years ago. As company expands, we have more customers, there is more rows in spreadsheet. Now, I found out the macro can't work on rows after 4xxx.

How can I change it? I see $A$2:$E$4412 in macro, so, I just need to change 4412 to 9999?

By the way, does this macro also involve any change the date? On the sheet "Linked summary", "meeting summary", "Final list", date column it will show 2016 , not 2017.

Thanks

the code is like that:

Please Login or Register  to view this content.

[Sintek]

Hi Ronlau123

Your post does not conform with rule3 of the forum. please apply so that we are able to assist.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[ronlau123]

Thanks. I edited the post, please help me.

[Sintek]

ronlau123

I am very certain that we will be able to simply this macro if we just knew the result required. The attached sample does not really reflect what you are trying to achieve.
Anyway, put this in the beginning of your macro

Please Login or Register  to view this content.

and change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

As far as the date concern...The current macro does not have any date implication. So the date entry is a manual one by the user as the information just gets copied and pasted over. Why not upload a sample of your workbook explaining in detail what you want to achieve ...do a manual mock-up depicting the result. I think it is time for a code update...

[ronlau123]

Thanks for your help.

Sorry I have incomplete version of sample. The original one with a lot of confidential information at company. I will try to make one more detailed sample. It is a long story.

You mean the code should be like that. Sorry, I am not good at Macro.

May I ask why the code is Range("A2:E" & lRow).Select but not Range("A2:E").Select

Thanks very much

Revised code should like that, may I ask am I right?

Please Login or Register  to view this content.

ronlau123

I am very certain that we will be able to simply this macro if we just knew the result required. The attached sample does not really reflect what you are trying to achieve.
Anyway, put this in the beginning of your macro

Please Login or Register  to view this content.

and change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

As far as the date concern...The current macro does not have any date implication. So the date entry is a manual one by the user as the information just gets copied and pasted over. Why not upload a sample of your workbook explaining in detail what you want to achieve ...do a manual mock-up depicting the result. I think it is time for a code update...

[meabrams]

Basically what sintek code does for you is finds the last row by using

Please Login or Register  to view this content.

lRow equals the Count of Cells Used in that Row

Then when you use

Please Login or Register  to view this content.

it will select all used cells in that column.

[protonLeah]

One way is to declare a variable at the top of the code to hold the last row number:
Dim LastLinkRow As Long

Then find the last row where you have:
Sheets("Linked Summary").Select
add:
LastLinkRow = Cells(Rows.Count, "A").End(xlUp).Row

Then
Range("$A$2:$E$4412").Select
becomes:
Range("A2:E" & LastLinkRow).Select

If the data in sheets LINKED SUMMARY were in a table you could simply have
Range("tableX").Select
instead of searching for the last row on the sheet.

Neither macro is adding or modifying any dates.

[ronlau123]

Hi, sorry every one,

it says Run-time error "438" Object does not support this property or method. How can I fix it?

May I ask should I also need to change another macro in excel, Finalize_list?

I edit the excel for using the macro that I get from this thread , if I do something wrong, please let me know

Thanks very much

[protonLeah]

lRow = Sheets("Linked Summary").Cells(Rows.Count, "E").End(xlUp).Row

[ronlau123]

Thanks very much. Sorry that still have problem.

After I use the macro , it has Run-time error '5'': Invalid procedure call or argument.

As I press debug, it highlight in yellow

Please Login or Register  to view this content.

I upload another sample with right macro.

Little bit why we need to use the excel file:

Sheet 1, 2 ,3 , another department will type client meeting info (date, time)
Sheet 1 means current month, sheet 2 is next month.

After typing info, I will use the macro Meeting_Separation
After using the above mention macro, the meeting information will be reformated and paste on sheet meeting summary . Sometimes, another department will input wrong format (not only type meeting date, they will input other stuff). So, after I check Sheet "meeting summary", I will use macro Finalize_List.

Then the info with our desired format will be on Sheet "final list" and I will send to another department, they will do their part.

[xlnitwit]

Hi

Remove the .Select part from that code.

[ronlau123]

Sorry, can you let me know which part? you mean I need to remove every ".Select" from macro? Anyone not mind to re post the right one to me?

Feel confusing

Please Login or Register  to view this content.

[xlnitwit]

From the line of code you posted saying that is where the debugger stops.

[ronlau123]

Sorry, still does not work

I try several times, still stop at

Please Login or Register  to view this content.

. Which part I do incorrect (I have already remove .select)? Please let me know.

Thanks



I use the code as following:

Please Login or Register  to view this content.

I also try to delete more, not work

Please Login or Register  to view this content.

[ronlau123]

Hi, Friends:

I make 3 sample spreadsheet. One is no macro used. another is one excel for after using marco: meeting_seperation. Another one is after using macro: after using Finalize_list macro

As you see the spreadsheet has 3 sheets , "1", "2", "3". Another department will input meeting information.

Sheet "1flag", "2 flag", "3 flag" has formula to link "1", "2", "3"

Then, i will use meeting_seperation macro, after that using Finalize_list macro

They are sample spreadsheet. Actual one has more than 100 accounting manager.

Why we using this template, try to convert information format

Thanks

[Sintek]

Hi Ronlau123

By now we are all confused because nothing makes sense. We are unsure of the result you require. Attached a sample workbook with before the code must run and then draw up a mock sheet of how you want the result to look. We are going around in circles and getting nowhere slowly.

[ronlau123]

Sintek:

Thanks. I attach the new spreadsheet. Sheet"Final List(mock result)" is added, so, you will see the result that I want.

Again, this is sample excel, actual one has more than 1000 buildings and hopefully you understand that I can't use that one.

Should I forget the old macro, re design the new one?