Help me on this

[Kranthi]

Hi,

I have a data, First column contains Dates and in second column there is some relevant data, Here i want help from you for third column. The third column each cell value depends on Date that is there in first column.

My question if first column 2nd cell reflects Date as 16/01/2015 then third column 2nd cell should reflect value as "Lot-1", if first column 3rd cell date is 17/01/2015 then third column 3rd cell should reflect value as "Lot-2" and so on.

Kindly help me on this.

Thanks,
Kranthi

[Kranthi]

Someone kindly help me on this.

[NoSparks]

You haven't supplied enough information.

Based on the two cell example you give, try this formula in C2 and drag down.

=IF(OR(A2=A1,A2=""),"","Lot - " & ROW()-1)

I suspect you posted this under macros and VBA because there is a lot more to it than what's been presented.
You should post a realistic example file of what you're actually working with that would answer the following.
Do dates appear more than once in col A ?
Are the dates intermingled ?
Can the dates and data be sorted ?

[Kranthi]

Hi Dude,

Thank you for the suggestion. Yes i agree i did not present full information. For your reference i have attached the Excel sheet in which first column contains Dates and third column Lot numbers.

Kindly note below points:
- If i mention any date that can be 20-01-2016 then Lot number should start from there in third column. For e.g, if a date starts with 20-01-2016 then Lot number should be in third column is "Lot-1" and Here we consider 21-01-2016 as holiday then if date is 22-01-2016 then lot number should be "Lot-2". if there is any date in the next cell of the first column after 22-01-2016 then it should reflect next lot number. if suppose date is 25-01-2016 in next cell of first column then lot number should be "Lot-3".
- If suppose date is repeated twice or thrice then then lot number should be same as it is reflected, for suppose for date 22-01-2016 lot number is "Lot-2" and date in next cell is again 22-01-2016 then lot number also should be same i.e., "Lot-2".
- Sometimes there will be already a value in Third column(Lot column) then it should not be changed. It should be as it is. Only blank cells should be filled in third column if a corresponding first column cell has date.

I have attached the excel sheet for your reference which helps you to understand more better.

Thanks,
Karthik

[NoSparks]

Give this a try, I think it does what you want with the data as presented.

Code: Select all

Sub LotNumbers()
    Dim rng As Range, cel As Range
    Dim ThisDay As Date, LastDay As Date
    Dim curLot As Integer
   
Set rng = Sheets("Sheet1").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each cel In rng
    If cel.Offset(0, 2).Value = "" Then ThisDay = cel.Value
    If ThisDay <> LastDay Then curLot = curLot + 1
    If cel.Offset(0, 2) = "" Then cel.Offset(0, 2).Value = "Lot-" & curLot
    LastDay = ThisDay
Next cel
End Sub


[Kranthi]

Hello Dude,

Thank you so much once again for your patience, understanding my exact need and writing the macro. Code is working absolutely fine.

Thanks a lot for your great help.

Thanks,
Karthik

[Kranthi]

Hi Dude,

One more small help i need from you.

My humble request you to kindly explain the code from starting line. As i am not good at macro, could not able to understand everything. Please write your explanation comments below the each macro code line. Your explanation helps me to apply this code wherever required for me in future.

Thanks,
Kranthi

[NoSparks]

The challenge is dealing with dates that can't have a lot number. This makes it necessary to keep track of two different dates, one for the current date you're dealing with and one for the last date that had a lot number.

By default variables are initialized as 0 and "", so aren't set to be anything prior to being used in the procedure.

Code: Select all

Sub LotNumbers()
    Dim rng As Range        'the range to work with
    Dim cel As Range        'individual ranges within the work range
    Dim ThisDay As Date     'the date of the current cel
    Dim LastDay As Date     'the last date a lot number got assigned
    Dim curLot As Integer   'the current lot number
   
'get the range to work with in column A
'from A2 to the last cell with data
Set rng = Sheets("Sheet1").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)

'deal with this range one range (cel) at a time
For Each cel In rng

    ' only deal with this cel if the lot number cell is empty
    ' make ThisDay variable equal the date of the cel being dealt with
    If cel.Offset(0, 2).Value = "" Then ThisDay = cel.Value
   
    'check if the date being dealt with is same as last date dealt with
    'if not then increase the lot number
    If ThisDay <> LastDay Then curLot = curLot + 1
   
    'if cel being dealt with has nothing in lot number column put in lot number
    If cel.Offset(0, 2) = "" Then cel.Offset(0, 2).Value = "Lot-" & curLot
   
    'for next loop to know what date was last dealt with
    LastDay = ThisDay

'move on to next cel
Next cel

End Sub


Hope that helps
NoSparks

[Kranthi]

Thank you for the detail explanation dude.

[Kranthi]

Hi Dude,

One last help on this, Kindly go through the attachment.

In the attachment, there are already two other macros along with the Lot number macro. With all this, count of macros is 3. But when i run all three macros at once i am not getting output of third macro i.e., "Lot" macro output. If i run "Lot" macro separately then i am getting the output.

Request you to kindly help me on this to get output of "Lot" macro as well when i run three macros at once.

Thanks,
Kranthi