Update value in a column from another worksheet depending on the date in a range

[RaghuPrabhu]

Our new pay system at work went live in August 2017.
This is the new pay calendar (called PayPeriods)



Start Date
End Date
Pay period


10 Aug 17
23 Aug 17
PP0001


24 Aug 17
06 Sep 17
PP0002


07 Sep 17
20 Sep 17
PP0003


21 Sep 17
04 Oct 17
PP0004


05 Oct 17
18 Oct 17
PP0005


19 Oct 17
01 Nov 17
PP0006



This is the table for members (called MemberList)


Name
Due Date
Amount
Processed in Pay Cal


Raghu
15 Jul 17
$248.00
PP0001


Vim
20 Jul 17
$354.00
PP0001


Abhi
10 Aug 17
$954.00
PP0001


Neelima
23 Aug 17
$134.00
PP0001


Raghu
14 Sep 17
$134.00
PP0003


Vim
21 Sep 17
$524.00
PP0004


Abhi
06 Oct 17
$332.00
PP0005


Neelima
20 Oct17
$158.00
PP0006


Raghu
06 Sep16
$456.00
PP0002


Vim
19 Sep 17
$159.00
PP0003


Abhi
03 Oct 17
$357.00
PP0004


Neelima
18 Oct 17
$852.00
PP0005



I want to update the Processed in Pay Cal column with the pay periods.
Anything with a due date on or 23 AUG 17 to be processed in PP0001 and the rest in the appropriate pay calendar. I need help with this codeing please.

Sub updateColunm4()
If duedate <= 23Aug17 then
Update column 4 to PP0001
Else
Update column 4 to the appropriate pay period
End if
End sub
Thank you

Regards

Raghu

[werafa]

Hi Raghu

one option is to use the 'case' command

it uses the logic


Select Case test_expression
Case condition_1
result_1
Case condition_2
result_2
...
Case condition_n
result_n
[ Case Else
result_else ]
End Select

there are many good tutorials on google

[RaghuPrabhu]

Hi Raghu

one option is to use the 'case' command

it uses the logic


Select Case test_expression
Case condition_1
result_1
Case condition_2
result_2
...
Case condition_n
result_n
[ Case Else
result_else ]
End Select

there are many good tutorials on google


Thanks werafa will try but may need help. I am a novice.

Regards
Raghu

[werafa]

set up each of your dates in the case statement

eg,


dim endDate as date
dim payCode as string

endDate = .......

select case endDate
Case is <= 23 Aug 17
payCode = "PP0001"
Case is <= 06 Sep 17
payCode = "PP0002"
......
end select



Excel will scan the list from top to bottom until the first true condition is found, and then execute the subsequent line(s)
you will need to confirm that the date formatting I've used is acceptable.