VBA macro expert

[LebD]

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.

 

[LebD]

Still trying to do it, my draft code if you can help:

Sub transfer()

Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long

Dim datasheet As Worksheet 'where is the data copied from
Dim Rento As String 'first variable
Dim i As Integer 'row counter

Set datasheet = "Vacant List"
Rento = datasheet.Range("H").Value
Date = datasheet.Range("R").Value
datasheet.Select
finalrow = Cells(Rows.Count, 1).End(x1Up).Row

For i = 5 To finalrow

If Cells(i, 5) = Rento Then 'if selection matches so when there is a duplicate or triple results then additional criteria
'If Cells (R, 5)> (cell and column S) then
'If cells (R, 5)< (cell and column S) then the above row should be copied next to this one and not the other way around
Range(Cells(i, 1), Cells(i, 42)).Copy 'cut row i from columns A to AQ
Range("A3000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll 'Paste the selection but how to slect the pasting in cells 43 and more.
End If

Next i


End Sub

 

[My Aswer Is This]

I have looked at your question for two days and you have not received a answer so I will ask a few questions.

You mentioned several different columns but then said the column I choose.
So it could be column A or B or G or P
So how is the script to know what column to check.
Then you said:
If B = busy, it will copy paste the row from A to F into column G

So this means copy all the data in columns A to F and put in column G
So if it has "Alpha" in A and B has "Bravo" and on and on Column G would say AlphaBravo and on and on is that true?
And how do we know what to search for.
Just a few of my questions?

Like how do we know to search for available?
If B= available, it will copy paste the row from A to F into column M

 

[LebD]

Thank you for your answer, at least someone is trying to help me ?
The first post was the "intention" I needed to start somewhere and complete the code by myself, but it seems too complicated.
Second post, I tried the code with 'explanations to what I need

I will explain further below the whole project:

I have a list of data that fills up Column A to AQ and hundreds of rows.

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 (for first row is row 5, cause above it there is headers), 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", then I want to copy all the data in this row from A8 to AP8, and paste it 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) so it can make 2 decisions:
If R8>S5, then copy paste the row A8 to AP8 to CG5 to DV5
If R8<S5, then it should instead copy the row A5 to AP5 to CG 8 to DV8

I have some exceptions, but those variables we can add them later.
Hope it is clearer now

 

[LebD]

I want to detect the rows that have the same value in column H (like duplicates values in cells in column H): so if H5 = H8
or H27 =H223 whatever

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

Way to many if and or statements here for me.
Like: Then if Q8 >< is not equal to "Vacant",
Vacant is a word. how can Vacant be Greater then or less then something?
And just way too many if then else statements for me to sort out.

 

[LebD]

"Vacant" is the word in the cell it should search for (it is not vacant as in cell empty from value). it could be anything else

 

[LebD]

If you need more info, I am available. I think only a pro VBA can write this code

 

[LebD]

Maybe is it better to start this way:

Sub transfer()

'Declare All Variables
Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
Dim Lastrow As Long
'Count number of rows & column
myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
For myCol = 8 To Lastrow
'Loop column H to check duplicate values & highlight them.
For i = 5 To myRow
Set myRange = Range(Cells(2, i), Cells(myRow, i))
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then

'If Cells (R, 5)> (cell and column S) then
'If cells (R, 5)< (cell and column S) then the above row should be copied next to this one and not the other way around
Range(Cells(i, 1), Cells(i, 42)).Copy 'cut row i from columns A to AQ
Range("A3000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll 'Paste the selection but how to slect the pasting in cells 43 and more.

End If
Next
Next
End Sub

 

[LebD]

This is the closest I can come to.. I polished the code and explained it. But Help is needed:

Sub transfer()

'Declare All Variables
Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
Dim Lastrow As Long

'Count number of rows & column
myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
For myCol = 8 To Lastrow

'Loop column H to check duplicate values & highlight them.
For i = 5 To myRow
Set myRange = Range(Cells(5, 1), Cells(myRow, 42))
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then

'if value of cell in column 17 = "vacant" cut row i from columns A to AP and paste it in row that has same value from AQ to CF
If Int(Cells(i, 17).Value = "Vacant" Then Range(Cells(i, 1), Cells(i, 42)).Cut Range(Cells(i, 43), Cells(i, 84))

If Int(Cells(i, 17).Value <> "Vacant" then
'If Cells date (i, 18) date is > (cell prior row, 19) date in the row compared to then copy the row range from A to AP and paste it in CG to DV of the row compared FROM (1st row where value is found in)
If Int(Cells(i, 18).Value > DateValue(cell of prior selected row(i, 19))) Then Range(Cells(i, 1), Cells(i, 42)).Cut Range(Cells(i, 85), Cells(i, 126)) 'cut row i from columns A to AP and paste it next to initial row

'If Cells date (i, 18) date is < (cell prior row, 19) date then the above row should be copied next to this one but not the other way around, then copy the row range from A to AP and paste it in CG to DV of the row compared TO (second row where value is found in)
If Int(Cells(i, 18).Value < DateValue(cell of prior selected row(i, 19))) Then If Int(cell of prior selected row(i, 18).Value > DateValue(cell(i, 19))) Then Range(Cells(i, 1), Cells(i, 42)).Cut Range(Cells(i, 85), Cells(i, 126)) 'cut row i from columns A to AP and paste it next to second comparison row


End If
Next
Next
End Sub