encode textbox values to all worksheets

[halshion]

Hello again everyone,

I want to encode the textbox values to all worksheets so I made a code, sourced from the net, modified slightly for my taste. Does not display an error but it doesnt do anything.
what may seems to be the prblem?

Set wb = Workbooks(Machine1.Value & ".xlsx")
    wb.Activate

    For Each ws In wb.Sheets
    
        'inserts start and date end values to range M4 and R4
        range("M4").Value = CDate(DateFrom.Value)
        range("R4").Value = CDate(DateTo.Value)
        
        datStartDate = range("M4").Value
        datEndDate = range("R4").Value
    
        lngStartDate = datStartDate
        lngEndDate = datEndDate

        Dim i As Date 'autofills date start to end using loop

            For i = 0 To (lngEndDate - lngStartDate)
                Cells(i + 3, 40).Offset(5 * i, 0) = lngStartDate + i
            Next i
    Next ws

 

[Legacy 143009]

Try:

Cells(i + 3, 40).Offset(5 * i, 0).Value = lngStartDate + i

 

[halshion]

Try:

Cells(i + 3, 40).Offset(5 * i, 0).Value = lngStartDate + i

Tried it still didn't encode. The code works if I declare a specific worksheet to encode, maybe i did something wrong with for each loop

 

[Legacy 143009]

Tried it still didn't encode. The code works if I declare a specific worksheet to encode, maybe i did something wrong with for each loop

Oh yes, btw you never use ws. I don't know if you need it for gettin values or setting the values but you should use it. To set values example:

ws.range("M4").Value = CDate(DateFrom.Value)
ws.range("R4").Value = CDate(DateTo.Value)

ws.Cells(i + 3, 40).Offset(5 * i, 0).Value = lngStartDate + i

Or getting values:

Range("M4").Value = CDate(ws.DateFrom.Value)
Range("R4").Value = CDate(ws.DateTo.Value)

Or maybe both, I don't know.. These are only my assumptions. You must adjust it according to your needs.

Why did you remove Beluga? He was cute

 

[halshion]

Oh yes, btw you never use ws. I don't know if you need it for gettin values or setting the values but you should use it. To set values example:

ws.range("M4").Value = CDate(DateFrom.Value)
ws.range("R4").Value = CDate(DateTo.Value)

ws.Cells(i + 3, 40).Offset(5 * i, 0).Value = lngStartDate + i

Or getting values:

Range("M4").Value = CDate(ws.DateFrom.Value)
Range("R4").Value = CDate(ws.DateTo.Value)

Or maybe both, I don't know.. These are only my assumptions. You must adjust it according to your needs.

Why did you remove Beluga? He was cute

yep tried it, though not the last one, since the datefrom and dateto values are from userform textboxes

For Each ws In wb.Worksheets
    
        'inserts start and date end values to range M4 and R4
        ws.range("M4").Value = CDate(DateFrom.Value)
        ws.range("R4").Value = CDate(DateTo.Value)
        
        datStartDate = ws.range("M4").Value
        datEndDate = ws.range("R4").Value
    
        lngStartDate = datStartDate
        lngEndDate = datEndDate

        Dim i As Date 'autofills date start to end using loop

            For i = 0 To (lngEndDate - lngStartDate)
                ws.Cells(i + 3, 40).Offset(5 * i, 0).Value = lngStartDate + i
            Next i
    Next ws

also tried substituting the range to cells format, also didn't work

For Each ws In wb.Worksheets
    
        'inserts start and date end values to range M4 and R4
        ws.Cells(4, 13).Value = CDate(DateFrom.Value)
        ws.Cells(4, 18).Value = CDate(DateTo.Value)
        
        datStartDate = ws.Cells(4, 13).Value
        datEndDate = ws.Cells(4, 18).Value
    
        lngStartDate = datStartDate
        lngEndDate = datEndDate

        Dim i As Date 'autofills date start to end using loop

            For i = 0 To (lngEndDate - lngStartDate)
                ws.Cells(i + 3, 40).Offset(5 * i, 0).Value = lngStartDate + i
            Next i
    Next ws

 

[Legacy 143009]

Tested. This worked for me:

For Each ws In wb.Worksheets
  
        'inserts start and date end values to range M4 and R4
        ws.Range("M4").Value = CDate(DateFrom.Value)
        ws.Range("R4").Value = CDate(DateTo.Value)
      
        datStartDate = ws.Range("M4").Value
        datEndDate = ws.Range("R4").Value
  
        lngStartDate = CLng(datStartDate) 'You should cast to Long
        lngEndDate = CLng(datEndDate) 'You should cast to Long

        Dim i As Date 'autofills date start to end using loop

            For i = 0 To (lngEndDate - lngStartDate)
                ws.Cells(i + 3, 40).Offset(5 * i, 0).Value = CDate(lngStartDate + i) 'Convert back again to date
            Next i
    Next ws

Thanks for bringing Beluga back again

 

[halshion]

Tested. This worked for me:

For Each ws In wb.Worksheets
 
        'inserts start and date end values to range M4 and R4
        ws.Range("M4").Value = CDate(DateFrom.Value)
        ws.Range("R4").Value = CDate(DateTo.Value)
     
        datStartDate = ws.Range("M4").Value
        datEndDate = ws.Range("R4").Value
 
        lngStartDate = CLng(datStartDate) 'You should cast to Long
        lngEndDate = CLng(datEndDate) 'You should cast to Long

        Dim i As Date 'autofills date start to end using loop

            For i = 0 To (lngEndDate - lngStartDate)
                ws.Cells(i + 3, 40).Offset(5 * i, 0).Value = CDate(lngStartDate + i) 'Convert back again to date
            Next i
    Next ws

Thanks for bringing Beluga back again

Thanks for the help

also cultured Beluga fan

 

[Legacy 143009]

Glad it did work