PDA

View Full Version : Copy Cell Values Using If Statements through VBA Code



nathandavies
04-20-2018, 07:09 AM
Hi, I'm after some help writing some code using if statements in VBA, i have never done this before so please bare with me.

This is what i'm trying to complete in the code.

1. if there is a date value in any cell on column AA1:AA4 that is equal to the date today then look at column W2:W4 to find the date.
2. then i want to copy the value from W2:W4 from any cell with a date in AA1:AA4 into Sheet1 Cell C25
3. i then want to copy (the using the concatenate function) any information from A2:A4 with E2:E4 to sheet 1 cells C19:C23 but only copy if there is a date in W2:W4 & AA1:AA4

I have created a mock up workbook with everything filled out for your assistance.

Thank you in advance for any help given on this. 22067
ND

offthelip
04-23-2018, 03:57 PM
Here you are try this:

Sub test()
With Worksheets("sheet2")
inarr = Range(.Cells(1, 1), .Cells(4, 35))
End With
indi = 19 ' the start row for the concatenated data
tdo = Date
' note I select the worksheet so that I can see what the code is doing. lots of people might say this is unnecesary but it does make for easy debugging
Worksheets("sheet1").Select
For i = 2 To 4
If inarr(i, 27) = tdo Then
Cells(25, 3) = inarr(i, 23) ' column W
Cells(indi, 3) = inarr(i, 1) & " " & inarr(i, 5)
indi = indi + 1
End If
Next i

End Sub

nathandavies
04-24-2018, 05:32 AM
offthelip, thank you for this code. i have no idea how it works but it does work on the example i sent through.in my workbook there are a few changes i have made. the list will be infinite not just 4 rows. i have updated my workbook and attached so you can see what i mean.

offthelip
04-24-2018, 06:28 AM
I have annotated the code to explain exactly how it is working.

Sub test()
' this code loads the range A1 to AI4 inot a variant array, doing this speeds up the VBA enormously because it doesn't need to keep swappiung between worksheets
With Worksheets("sheet2")
inarr = Range(.Cells(1, 1), .Cells(4, 35))
End With
indi = 19 ' the start row for the concatenated data
' this loads today's date into variable tdo, this is to avoid calling the function date loads of time once again just to make it faster
tdo = Date
' note I select the worksheet so that I can see what the code is doing. lots of people might say this is unnecesary but it does make for easy debugging
Worksheets("sheet1").Select
' set up aloop to run from rows 2 to 4, note this also has to tie up with how much data is loaded into the array at the top, ( currnetly just 4 lines0
For i = 2 To 4
' compare the value in inarr(i,27) which (2,27) for the first loop, then 3,27, etc. the i corresponds to the row in sheet 2 and the 27 corresponds to the column, which is AA ( the 27th letter)
If inarr(i, 27) = tdo Then
' if they are the same then copy the value from the same row column 23 (W) to Cell C25
Cells(25, 3) = inarr(i, 23) ' column W
Cells(indi, 3) = inarr(i, 1) & " " & inarr(i, 5) ' copy the values for the same row column A and E to the cells initally row 19 column 3 (C)
indi = indi + 1 ' increment the row count so the next data found goes on the next row,
End If
Next i

End Sub




I had a quick look at yor workbook and straight away I can see a problem with your system design: in Sheet 2 you have 6 rows which have got dates in them, yet in sheet1 where you want the data copied to, there are only 5 blank rows at C19 to copy the data to, so my current software will overwrite the bit where it says Courier: Collection. Presumable at other times there might be even more dates.
Other questions that immediately spring to mind: does the data on sheet2 always start at row 40?
Is the list varianble in length, i.e. should the code automatically find the last row with data in?

nathandavies
04-24-2018, 06:50 AM
I will have a read through the annotated code and so i can amend in the future.

This is a design error, there could be more copied from the sheet. I will have to move the constant information further down the sheet or think of something else, maybe add a new row in every time.
the data always starts at row 44, and yes should find the last row with a todays dates in both Z & AD columns.

offthelip
04-25-2018, 12:55 AM
Don't add a new row everytime unless you want to create very slow vba, excel takes ages to add a new row because it has to move everything below that row down a bit. A much better way would be to either write the stuff below it in automatically (my preference) , or load it into a variant array from the worksheet, delete it on the worksheet and then write it into the correct place at the end.

nathandavies
04-25-2018, 01:15 AM
after looking into this, i have moved all the worksheet around so the data will be at the bottom, i have also added a second column so that if the first 13 cells are full move to the next column.

i have once again updated my worksheet so you can see what i'm trying to achieve.

thanks.

offthelip
04-25-2018, 04:27 AM
I have updated my code to tie up with your latest workbook, One thing you have done a lot on this workbook is "Merged Cells" If you are going to use VBA I strongly advise against merging cells they are very difficult to handle in VBA , use the Fromat cells "Center across selection" instead. It looks the same but causes fewer problems
The other thing I have done is to write all the additionla text into the sheet from VBA. This allows me to position the text which needs to be below the list of items flexibly to take account of how many items there are in the list. It will always appear with three blank lines after the last item.

Sub test()
With Worksheets("sheet2")
' Note I had to change this to Z from AA because you have merged cells
' Merged cells are difficult to handle in vba so: AVOID MERGING cells, use Format Center across selection instead
Lastrow = .Cells(Rows.Count, "Z").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(Lastrow, 35))
End With
indi = 27 ' the start row for the concatenated data
tdo = Date - 1
' note I select the worksheet so that I can see what the code is doing. lots of people might say this is unnecesary but it does make for easy debugging
Worksheets("sheet1").Select
' clear all the data from sheet1
Range("A1:Z50") = ""
Range("A1:Z50").Font.Bold = False



For i = 44 To Lastrow
If inarr(i, 30) = tdo Then
' Avoid merged cells!!!
Cells(18, 6) = inarr(i, 26) ' column W
Cells(indi, 3) = inarr(i, 4) & " " & inarr(i, 8)
indi = indi + 1
End If
Next i

Cells(17, 2) = "Ref:"
Cells(18, 2) = "Courier"
Cells(19, 2) = "FAO:"
Cells(20, 2) = "Address:"
Cells(27, 2) = "Goods"
Cells(18, 5) = "Date:"
Range(Cells(17, 2), Cells(27, 2)).Font.Bold = True
Range(Cells(18, 5), Cells(18, 5)).Font.Bold = True


Cells(indi + 3, 2) = "PLEASE REPORT ANY DAMAGES WITHIN 48HRS OF DELIVERY"
Cells(indi + 4, 2) = "ANY DAMAGES / CLAIMS AFTER WILL BE NOT APPLICABLE "
Cells(indi + 6, 2) = "Rec’d By: ……………………………………………………..………"
Cells(indi + 8, 2) = "Print Name: ……………………………………………………...……"
Cells(indi + 8, 2) = "Date: ………………………………………………………………….."
End Sub

nathandavies
04-25-2018, 07:21 AM
Thanks for your help on this, i'm going to complete some testing over the next couple of days on my main workbook.

thanks!