CODEWITHSUNDEEP
vba
Graham
Graham

Reputation: 57

VBA compare 2 values in different worksheets if they match copy specific data from row to another row

I am struggling with a VBA problem. I have 2 worksheets in the same workbook "Rota" and "sheet3" column "a" in Rota has a list of names columns "B"-"AQ" are dates Column "a" in sheet3 has a list of names columns "B"-"AQ" are dates Against each name in sheet3 there are cells with values "X1" "No" "Yes" I need to compare the names and if they match, look in the row matching the name in sheet3 and copy only the X1 values into the correct date cells for the matched name in Rota.

I have added a picture to demonstrate the 2 sheets, you will notice that there are multiples of the same date, that is because the dates are split into shifts so there will be up to three shifts for each date so the dates would have to be read Sequentially from sheet three and matched in sequence to Rota. The dates will always appear in the same sequence in each sheet but not in the same corresponding cells, example rota rota s6 and sheets3 s6.

this has been driving mad trying to work out the best way of doing this

Example Hopefully my explanation is good enough for someone to have a crack at this

Edit: Code from comment

Dim i As Long 
Dim j As Long 
Sheet1LastRow = Worksheets("availability").Range("A" & Rows.Count).End(xlUp).Row 
Sheet2LastRow = Worksheets("allocation").Range("A" & Rows.Count).End(xlUp).Row 
For j = 1 To Sheet1LastRow 
For i = 1 To Sheet2LastRow 
If Worksheets("availability").Cells(j, 1).Value = "Paul Wing" = Worksheets("allocation").Cells(i, 4).Value = "Paul Wing" Then
Worksheets("Sheet1").Cells(j, 2).Value = Worksheets("Sheet2").Cells(i, 1).Value 
Worksheets("Sheet1").Cells(j, 3).Value = Worksheets("Sheet2").Cells(i, 2).Value

Upvotes: 0

Views: 553

Answers (1)

user7857211
user7857211

Reputation:

These are the (main) steps you need for your task:

  • find value in worksheet
  • copy value into string
  • loop through cells/range
  • copy data between worksheets

Use the search engine of your choice and add vba excel in front of these steps. You will find plenty of example code that will get you started.

Create minimal example sheets and test your code there before applying it to the actual workbook. If you get stuck with any of those tasks, feel free to ask more question which include the specific code that you need help with.

Keep in mind that SO is not a place to ask questions like "how to start on this", but rather about specific question to specific code.

Upvotes: 1

Related Questions