VBA macro expert

LebD

New Member
Joined
Sep 30, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hello
I need to write a macro code for excel that does the following:

You have data in columns A to F like the pic
Macro should be able to check whether the column I choose (in this example column A) has the same name in different rows, then condition filled, it will check the column I designate (in this example column B for availability but can be column 5 or another).

If B = busy, it will copy paste the row from A to F into column G
If B= available, it will copy paste the row from A to F into column M

The order of which row section should be first and the other row with same name should be second or third, depends on the date in column E. the earlier (older) should be first in the row.
 

Attachments

  • testing.png
    testing.png
    24.7 KB · Views: 41
I think I made the right code now. BUT I NEED TO REPLACE THE VALUE = VALUE with copy paste.
Give me your opinions:

Sub dup_cp()

Dim i As Integer
Dim j As Integer
Dim k As Integer

With Sheets("Vacant List")
j = Application.CountA(.Range("A:A"))
'counts the number of filled in rows

For i = 5 To j
'it starts from line 5 on purpose, the numbers start from that line
For k = i + 1 To j
If .Cells(k, 8).Value = "Duplicate Value" Then GoTo skip_dup
'it skips the line that has already been detected as duplicated

If .Cells(k, 8).Value = .Cells(i, 8).Value Then
'it finds the duplicate value in the eight column

If .Cells(k, 17).Value = "vacant" Then
.Range(.Cells(i, 43), .Cells(i, 84)).Value = .Range(.Cells(k, 1), .Cells(k, 42)).Value
'it checks whether the cell i17 has the word "vacant" in it

Else
If .Cells(k, 18).Value > .Cells(i, 19).Value Then
'it compares the 18th column values (the modified duration of the components) and keeps the earlier date for comparison reasons
.Range(.Cells(i, 85), .Cells(i, 126)).Value = .Range(.Cells(k, 1), .Cells(k, 42)).Value
Else
.Range(.Cells(k, 85), .Cells(k, 126)).Value = .Range(.Cells(i, 1), .Cells(i, 42)).Value
End If

skip_dup:
Next
Next
End Sub
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi

So i have also been monitoring this thread.
And it would appear you need help complieting your project.
But i would say you have not been very clear with what you require.

I am going o have another read through your requirements.

Dave
 
Upvote 0
I want to detect the rows that have the same value in column H (like duplicates values in cells in column H), then for those rows detected, check out 2 more variables. If the cell in the row in Column Q, and when it detects the row with similar value somewhere on the sheet, lets say it is on row 8 (but it could be anywhere in the sheet)that would be Q8), so then if Q8 = "vacant" (the word not empty cell), then I want to copy all the data in this row from A8 to AP8, and paste the row NEXT to the row that has the same value in column H (H5), so paste it in cells AQ5 to CF5. Then if Q8 >< is not equal to "Vacant", it should look to cell R8 (start date) and compare it with cell S5 (end date of first row it found) so it can make 2 decisions: If R8>S5, then copy paste the 2nd row it found row A8 to AP8 to CG5 to DV5 (of the first row it found) If R8<S5, then it should instead copy the first row it found row A5 to AP5 to CG 8 to DV8 (next to the second row)

I wrote the following code above without copying (cutting) and pasting which is the closest to what I want but the criteria are not working well to get the result I want.
It finds duplicates but only copies the last row with the duplicated value not all the rows. Duplicates found can be up to 5 maybe.
Also the criteria for the dates dont seem to be working as i wish in the selection.
Once I see first the result, once I am confident in the code I will then remove the =value and replace it with cut and paste.
Let us work it step by step.
So the code above is working to a certain extent.
I have to do cut and paste and delete the row where it was cut.

I will post an example below
 
Last edited:
Upvote 0
Check below the example, i kept the values that are interesting for us and grouped the others

1. detecting duplicate values in column H, it finds Data2 in 2 rows, data7 in 4 rows etc.. so it should cut and paste one of the rows detected. which one? then it go to 2nd criteria
2. it checks the word vacant in column Q. if here is vacant it cut and paste the 42 cells in the row next to the duplicate. if no vacant, then it cuts in paste it in the 84th cell and onward.
3. it checks the dates per the code above to know which one should be cut and pasted first, the first row or the 2nd or the 3rd etc...
if start date is > end date, it should go on the right. if not, it stays on the left.

if we can get to this point already, then finer tweaking could be done to get to my objective
 

Attachments

  • Source.png
    Source.png
    38.6 KB · Views: 6
  • target.png
    target.png
    67.5 KB · Views: 6
Upvote 0
Seriously? nobody knows? it seems pretty simple on paper, but I am not a coder.

Can we try it out step by step then.

Let us focus first on this line of code:

For i = 5 To j
'it starts from line 5 on purpose, the numbers start from that line
For k = i + 1 To j
If .Cells(k, 8).Value = "Duplicate Value" Then GoTo skip_dup
'it skips the line that has already been detected as duplicated

In the example in my screencaps above, I am comparing all the rows found together. but my current code: after finding the rows with the duplicate values, instead of comparing each one with the previous, all together, it skips them all and only compares the last row found with the all the previous ones. so my output is giving me the last row of the batch only.
 
Upvote 0
I have read through everything you have posted.

Honestly i just cannot understand exactly what you are trying to achieve.

But it dosent sound like it should be difficult.

Can you just write, not in code, but a step by step of what you want to happen.

Personally im happy to help if i can.

i will ask questions about your step by step, then try to form a solution.
 
Upvote 0
Thank you

So let's see what we can do. Did you read the previous post with the screenshots, how it is and what i want?
Anyway, can we work it step by step please.

Let us focus first on this line of code:


For i = 5 To j
'it starts from line 5 on purpose, the numbers start from that line
For k = i + 1 To j
If .Cells(k, 8).Value = "Duplicate Value" Then GoTo skip_dup


In the example in my screencaps above, I am comparing all the rows, that are found to have the same value in column H, together group by group. then from this group I need to cut and paste the row in the other columns based on criterias found in column Q and by comparing the dates in column R and S.
So first if there is the word vacant in column Q, the duplicate row should be cut and pasted in the corresponding columns (AX to BB to make it simple).
If the word vacant in not in column H, then 2 things can happen when the rows has the same value in column H. We compare the dates. and the most recent ( from the 2 rows found is cut and pasted in CJ to CR. IF we find more than 2 row. let us say 4 rows like for Data7 in example above, then it should copy and paste rows (instead of cutting) in successive order (based by sorting of dates) and then cut and paste the final one.

but my current code: after finding the rows with the duplicate values, instead of comparing each one with the previous, all together, it skips them all and only compares the last row found with the all the previous ones. so my output is giving me the last row of the batch only in the right columns.
 
Upvote 0
Ok. So just focusing on the 1st bit.

In your data. The 1st group is h5 and h6. Data2

So we are only doing stuff with row 5 and 6 in the 1st loop. The it will be 7-9 data4 and so on ?
 
Upvote 0
Yes. exactly. the code I wrote up does this already. it singles out the rows with the same values in column H.
The tricky part is the second bit.
In the case of Data2, it is easy because second criteria filled: it finds the second row has a vacant word in column Q and so cut paste (for now i put =value for simplicity) but it shoudl insead cut and copy the row with the vacant word in it on the cells nest to the first row.
We go back to Data4 later.
It gets complicated with Data 7 because 2nd criteria not filled. so it looks for the 3rd criteria which is the dates in column R to compare it with the date is column S in the previous found row.
 
Upvote 0
for 3rd criteria, if date in second row R cell is > the date in cell S of previous same value row, then it should cut and paste it in the cells on the same row of the first row but starting from CJ to CR. Because I want to keep the centered columns empty for rows with vacant word in it. so it skips that space and paste it after.
now that would ideal setup for data7 if it found only 2 rows.
Here we have 4 rows with same value. so it should compare the dates, put the more ancient one first. then COPY (not cut because we have more than 2 rows) and paste the 2nd date.
copy and paste the third based on the date on the same row as the 2nd date. and lastly CUT and paste (because the last of the bunch) the 4th on the same row as the third .

the code I wrote above is only copy pasting the last row of the bunch (lets say the 4th one) next to each of one, and 2 and 3. and I need to fix it to reflect the above.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top