How to Copy Date to cells in Multiple Worksheets

[raul.s]

Hi

I am new to Vba, and struggling

I am trying out various functionalities in different subroutines and i intend to merge them together at some point.


I have two questions:

Q1 )
I use one subroutine to capture date as '28-Jul-11'

Dim str As String, str1 As String, datex As String
Public Sub DateTest()
str = Range("A2").Value
datex = Mid(str, 22, 12)
MsgBox datex
End Sub​

I would like to have this date in dd/mm/yyyy format. Would it be possible?

Q2)
In another Subroutine:

I have 50 worksheets in my workbook. Each worksheet can have varying number of records.

If worksheet1 has 10 records, I copy a row from my input worksheet to the 11th record (fields D11, E11, I11 are the only ones filled).

Here I would additionally like to move the 'datex' from subroutine in Question 1 to C11 (C has values in Date Format). Problem is, I am not able to hard code 11. It can be C11, C12 or anything. Can this be solved?

I have spent quite a bit of time browsing through posts before deciding to write in.

Looking forward to your suggestions, I see many advanced users here. Hence hopes are high.

Thank you, and I could say that many more times.

 

[Philip_Trick]

'For Q1:
'With your "datex", specify a new variable as a Date

Sub Example()
Dim datex as String
Dim dateN as Date
datex = "14-03-11"
'Convert the string into a date using "DateSerial" -- works just like the Date function in an Excel Formula.
dateN = DateSerial(Right(datex,2),Mid(dateex,4,2),Left(datex,2))

'Set the format for the cell you're writing the value to:
Range("A3").NumberFormat = "dd/mm/yyyy"

'Put the new date value into the appropriate cell.
Range("A3").Value = dateN
End Sub

'For Q2:
'It sounds like you're looking for the last row of data in a worksheet.
'There are some different approaches for this, but here's one:
Function get_LastRowNumber(sheetIndex) as Long

Dim rowNumber as Long
Dim workingRange as Range
'Get the last row in the current "UsedRange"
rowNumber = Sheets(sheetIndex).UsedRange.Rows.Count
'Replace B with a column that will have data in it that you're looking to add to.

If Sheets(sheetIndex).Range("B"&rowNumber) = "" Then
'If the last used cell in that row is blank, then I need to go up to find the last entry.
rowNumber = Sheets(sheetIndex).Range("B"&rowNumber).End(xlUp).Row + 1
Else
'If the last used cell in that row isn't blank, then I need to go down one cell and expand my UsedRange
rowNumber = rowNumber + 1
End If

get_LastRowNumber = rowNumber

End Function

'That function will give you the row number for the sheet name or sheet index that you put in.
'Any other subroutine can then do:
'Sheets(targetSheet).Range("E"&get_LastRowNumber(targetSheet)).Value = Sheets(sourceSheet).Range("A2").Value.

 

[raul.s]

Philip, that was very quick! I am going to work on it now, Thank you.. a lot!