Macro for rearranges cells

[Guest]

Hi experts:

I have and excel sheet as a result of importing the report from an
accounting software/foxpro as follows:
Job name Material Budget
job No. Prj. Manager Material Actual
1 a123 Buldng A 15, 000
2 Mr. X 12.000
3

One job number takes 2 rows, that is the layout of the report extracted
*consits of hundred jobs)
How can I make a macro in order to change the above to have layout to be:
job No. JobName Prj Manager Material Budget Material Actual;
a123 Buldng A Mr. X 15,000 12,000

In order for me to use #vlookup" to return data in another summary sheet.

Any idea is appreciated.

Frank

 

[Guest]

Hi

If it is fixed for 2 rows, that a simple formula will help:

what it looks like:

A B C
1 Job name Material Budget
2 job No. Prj. Manager Material Actual
3 a123 Buldng A 15, 000
4 Mr. X 12.000
5 B234 BULng B 30,000
6 Mr. ABC 50.000

Step 1: Add 2 formula on next 2 columns by just copying the 2 values

A B C D
E
1 Job name Material Budget
2 job No. Prj. Manager Material Actual
3 a123 Buldng A 15, 000 Mr. X (+b4)
12.000 (+c4)
4 Mr. X 12.000 BULng B
30,000
5 B234 BULng B 30,000 Mr. ABC
50.000
6 Mr. ABC 50.000 ???????
?????

Step2, copy formula for col D and E, for ALL rows, i know row 4 and row6
won't look good but will fix later

Step 3, copy and paste value for Col D and E

Step 4, remove row 4,6,8... (how? Sort by Col A then delete thow rows with
missing value)

if you automate the process using macro, for Step 4, try to use
select.xldown to locate the empty cell then use offset go to next cell then
select down and delete the entirerow.

Hope this help and happy coding.

Leung

 

[Guest]

you are genius Leung, your advice is very helpful, the only thing I need to
learn is about Macro step 4. where can we find on the website the use of
xldown and offset functions. I have to see the example to help me understand.
I have to admit that this forum is very helpful. I can now make conversion of
Lotus macro into excel macro and also developping a MSAccess supplier invoice
tracking, is just by the help of many friends in this forum. I took the
basic short course 20 years ago which I mostly forgot, and now I can refresh
my knowledge and find out something new.

Thank you very much Leung, greetings from Jakarta, Indoensia.

Frank

 

[Guest]

You are welcome, Frank

For Step 4, it's very common that we need to locate the starting point of
the data range that we need to manipulate. This can be done by recording a
macro. Coding usually like this:

'always to home is a good idea
Range("A1").Select
'replace the "name" with whatever you want to locate
'a little trick is to replace "Activate" with "Select" so the cell is being
selected
Cells.Find(What:="name", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select

'use offset to move next rol / col , the first 1 is row, the second one is
col, use 0 for not move and use -1 to move back

Selection.Offset(1, 1).Select

'the you can locate where the data start then use xlright and xldown to
select all
'data that you want to perform anything
'This is alway helpful that you can start from the bottom or right most
'just use macro recorder to record it, use it with offset will become very
very powerful

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

'Do what every you like with this selection.

End Sub


hope this help.

Leung
Hong Kong

 

[Guest]

Thanks Leung, your advice opened my mind to the more advanced manipulation of
data. With your fists advice I can do now arranging cells for many sheets,
and use macro to do it.

I tried to follow your 2nd advice, but since the number of data rows for
each sheet is not the same, the follwing part of macro WILL NOT WORK for the
next time, if the number of rows for each sheet keep changing.

Data from Sheet E00 (2) being pasted to SUMMARY WBLA in RAnge "A10". if next
month the mumber of rows changed, and it starts from A10. Dont't you think
the result could be wrong?

DONT'T YOU THINK WE SHOULD MAKE A DYNAMIC RANGE?, but how can we make it

See below part of the VBA:
Sheets("A00 (2)").Select
Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A8:X10").Select
Selection.Copy
Sheets("SUMMMARYWBLA").Select
ActiveSheet.Paste
Range("A9").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("A12").Select
Sheets("E00 (2)").Select
Range("A9").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A9:Y11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SUMMMARYWBLA").Select
ActiveSheet.Paste
Range("A10").Select

 

[Guest]

Hi,

for your code below, why you need to select down twice??
if there is some missing cell in your row, try not to use that columne
because you will miss some row.

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A8:X10").Select

The point is why you make a selection of Range("A8:X10") ??
can you paste the data here so I can give you some advise?

same case for below code:

Range("A9:Y11").Select

regards

Leung

 

[Guest]

Hi Leung.

As I mentioned we have 2 category of sheets:
1. there are 8 sheets, on which I have modified the format leaving the job
numbers with alll their information in one line per job. After doing data
sort by job number, one sheet could only be 2 jobs and for the oher sheets
could be many

2. The other category is summary sheet named " Summarywbla". The
Range("A8:X10").Select is the range that I copied to "summarywbla".from A00
point 1 above catagory.

You see XLdown twice is the result of the recording macro, maybe I hit twice
I dont know.

I know that if we used End, it goes thru the occupied cells.

Thanks

Frank