Unique Problem of Inputting date across all cells with value

[anuradhagrewal]

Hi
I have a unique problem.
I have multiple csv files and I need that the date value (ddmmyyyy format) mentioned in B3 column to be printed from D5 onwards in D column across all cell where a value is mentioned in C column.
I shall merge all these csv files into one xlsx file through VB code and then want the VB code to run across all merged files as the date is in a fixed position of B3 across all such worksheets.

Is this be possible.

Regards

Anuradha

 

[fadee2]

hi,
Lets break down your query....

As for your 1st question:

I need that the date value (ddmmyyyy format) mentioned in B3 column to be printed from D5 onwards in D column across all cell where a value is mentioned in C column.

try the following code

Private Sub dataeinput()
lr = Range("A1").SpecialCells(xlCellTypeLastCell).Row
d = Left(Range("b3"), 2)
m = Right(Left(Range("b3"), 4), 2)
y = Right(Range("b3"), 4)
For i = 5 To lr
    If Cells(i, 3) <> "" Then
        Cells(i, 4) = Format(m & "/" & d & "/" & y, "dd/mm/yyyy")
    Else
    End If
Next
End Sub

For your 2nd question:

I shall merge all these csv files into one xlsx file through VB code

have a look at here. It is a complete guide on how to merge csv files into a single excel file using vba.

and for your 3rd question:

then want the VB code to run across all merged files as the date is in a fixed position of B3 across all such worksheets.

try the code below

Private Sub dataeinput()
Dim ws As Worksheet
For Each ws In Worksheets

lr = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row
d = Left(ws.Range("b3"), 2)
m = Right(Left(ws.Range("b3"), 4), 2)
y = Right(ws.Range("b3"), 4)

    For i = 5 To lr
        If Cells(i, 3) <> "" Then
            Cells(i, 4) = Format(m & "/" & d & "/" & y, "dd/mm/yyyy")
        Else
        End If
    Next i
Next ws

End Sub

hth...

P.S.
Date in your wokrsheets in range B3 should be formatted as text, to take the prefix 0 as part of date for each single figure date (1 to 9 i.e.)

 

[anuradhagrewal]

Hi
Thanks for your reply.
But the code you have mentioned is not working.
I am copying it in the code in
Visual Basic--->Insert--->Module
But the I am not able to run it in the excel file. Kindly guide.

Much Obliged

Anuradha

 

[fadee2]

try

Sub dataeinput()
Dim ws As Worksheet
For Each ws In Worksheets

lr = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row
d = Left(ws.Range("b3"), 2)
m = Right(Left(ws.Range("b3"), 4), 2)
y = Right(ws.Range("b3"), 4)

    For i = 5 To lr
        If Cells(i, 3) <> "" Then
            Cells(i, 4) = Format(m & "/" & d & "/" & y, "dd/mm/yyyy")
        Else
        End If
    Next i
Next ws

End Sub

 

[anuradhagrewal]

Hi
Thanks once again the code does what I had envisaged.

But my kind request is that the date is not recognized by excel as it come in txt file.

So if the date mentioned is 06122020 when I run the code it does not copy the date in the way it is mentioned and populates the column with "//".

So if I may request you that the date in the B3 be picked up as it is and copied across all rows where there is data will suffice my purpose.

Much obliged again

Anuradha

 

[fadee2]

Hi
Thanks once again the code does what I had envisaged.

But my kind request is that the date is not recognized by excel as it come in txt file.

So if the date mentioned is 06122020 when I run the code it does not copy the date in the way it is mentioned and populates the column with "//".

So if I may request you that the date in the B3 be picked up as it is and copied across all rows where there is data will suffice my purpose.

Much obliged again

Anuradha

you are welcome.
furthermore, if you want to pickup the value in B3 as is then use

Sub dataeinput()
Dim ws As Worksheet
For Each ws In Worksheets

lr = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row

    For i = 5 To lr
        If Cells(i, 3) <> "" Then
            Cells(i, 4) = ws.Range("b3")
        End If
    Next i
Next ws

End Sub

hth...

 

[anuradhagrewal]

Splendid this worked as a dream... You are a real sweetheart.
It has saved me a lot of work.
I also have now discovered a new challenge.
Actually there are to be made into individual work sheets.
The date field B3 remains constant across all the worksheets.
I can run this code sheet by sheet buy I see there could be close to about 200 odd sheets.
Is there way that I add all the sheets into the workbook and then the given code runs across all the worksheets in one go.
If so ...... it goes without saying you will be my angel of the week.

But thanks any way for your help

 

[fadee2]

??? you are sweet yourself ???. thanks for the flatering praises. ?
and yes this will work on all your worksheets in a single workbook.
if uneed a macro to merge ur csv files in a sungle workbook wuth multiple worksheets, thrn see Merging .csv files into multiple worksheets within one workbook
step by stp guide to do so...

 

[anuradhagrewal]

Ohh thanks for saying so...
I tried running the code and this is what I found out
Scenario 1
It runs like a dream if there is only data on one sheet i.e sheet 1----B3=06122020 then the script will populate all the columns. Here there are sheet 2 and sheet 3 but they are blank.

Scenario 2
Now there are 3 worksheets with data --->Sheet 1----Sheet 2-----Sheet 3.
Sheet 1---B3=06122020
Sheet 2---B3=07122020
Sheet 3---B3=08122020
Now when I run the code on each sheet manually then value captured is B3 value in the last worksheet.
For eg : Sheet 1 ----Sheet 2---- Sheet 3 will populate in each of the individual sheets the date value of 08122020 which the value in sheet 3--B3.
How can I resolve this problem.

My objective now is to
Populate the columns in each respective individual sheet with value in B3 of that sheet. Also since the value of B3 is what I want to capture in each worksheet is it not possible to run the code once and all the values are updated in the respective worksheets
For eg:
I execute the code and value of B3 in sheet 1 is populated in the 4 column, value of b3 in sheet 2 is populated in the 4 column and so and so forth. It is quite tedious going sheet by sheet and running the code as there are close to 200 such worksheets in the workbook.
This is my problem Can u plzzzzzz help me dear.?

Thanks
Anu

 

[fadee2]

The code will automatically select b3 value in each worksheet before proceeding with date dumping. In case solution is not clear
try the following code:

Sub dataeinput()
Dim ws As Worksheet
cws = Worksheets.Count      'Counts number of worksheets in your workbook

For Each ws In Worksheets

lr = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row
        ws.Activate         'Moves to worksheet that needs date dumping
        For i = 5 To lr
        If Cells(i, 3) <> "" Then
            Cells(i, 4) = ws.Range("b3")

        End If
    Next i
        If ws.Index = cws Then      'Matches Worksheet number with total number of sheets
        MsgBox "All Done"
    Else
    End If
    Application.Wait Now + TimeValue("00:00:01")    'the code waits for 1 second before proceeding to next worksheet
Next ws

End Sub

I have added few additional lines in the code that would show you step by step what is being done on each worksheet. Refer to text in green.