VBA Find method setting ranges from strings inaccurately
up vote
0
down vote
favorite
I am receiving inaccurate date values in a VBA loop. The code I am using is below. I have a table of start dates and end dates some of which are February dates begining in "02". With all dates in the table that begin with "02" I am receiving string values that start with "12" with the remaining portion of the date the same. It looks as though there may be an error in the data type or something else. How can I define the dates as they are so that I may return accurate dates as they are written in the cells in the worksheet? Thanks!
Dim aa As Integer
Dim StartDate As String
Dim EndDate As String
Dim RngStart As Range
Dim RngEnd As Range
Dim RngStartR As String
Dim RngStartRng As Range
Dim RngEndR As String
Dim RngEndRng As Range
Dim RngXR As String: RngXR
ActiveWorkbook.Sheets("ActiveSheet").Range("C7").value
Dim RngXR2 As String: RngXR2 =
ActiveWorkbook.Sheets("ActiveSheet").Range("C8").value
Dim sh As Worksheet
Dim chrt As ChartObject
Dim ch As Chart
Dim zz As Integer
Dim NumObs2 As Long
NumObs2 = Sheets("AllDistanceMeasures").Cells(Rows.Count, 10).End(xlUp).Row
For aa = 5 To NumObs2
StartDate = Sheets("AllDistanceMeasures").Cells(aa, 9).value
EndDate = Sheets("AllDistanceMeasures").Cells(aa, 10).value
If StartDate <> "" Then
Set RngStart = Sheets("ActiveSheet").Cells.Find(What:=StartDate,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1)
Else
MsgBox "StartDate variable for " &
Sheets("AllDistanceMeasures").Cells(aa, 9).Address & " not found",
vbExclamation
Exit Sub
End If
RngStartR = RngStart.Address
If EndDate <> "" Then
Set RngEnd = Sheets("ActiveSheet").Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1)
Else
MsgBox "EndDate variable for " & Sheets("AllDistanceMeasures").Cells(aa, 10).Address & " not found", vbExclamation
Exit Sub
End If
RngEndR = RngEnd.Address
ActiveWorkbook.Sheets("LowDistCharts").Activate
Set sh = Worksheets("LowDistCharts")
Set chrt = sh.ChartObjects.Add(0, 0, 300, 300)
Set ch = chrt.Chart
Do While ch.SeriesCollection.Count > 1
ch.SeriesCollection(1).Delete
Loop
With chrt
.Height = 300
.Width = 300
.Top = 1 + ((aa - 4) * 300)
.Left = 1
End With
With ch
.HasTitle = True
.ChartTitle.Text = aa & " " & StartDate & " to " & EndDate
.ChartTitle.Font.Size = 8
.ChartType = xlLine
.SeriesCollection.NewSeries
.SeriesCollection(1).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngXR, RngXR2)
.SeriesCollection.NewSeries
.SeriesCollection(2).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStartR, RngEndR)
.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(3).Delete
.HasLegend = False
End With
For zz = 0 To NumObs - 1
Sheets("ActiveSheet").Range(RngEndR).Offset(zz, 0).Copy
Sheets("LowDistCharts").Cells(5, aa + 5).Offset(zz, 0).PasteSpecial xlPasteValues
Next zz
Next aa
excel vba
edited Nov 11 at 6:50
Cindy Meister
13.4k101934
asked Nov 11 at 4:32
Scott
1
|
show 1 more comment
up vote
0
down vote
favorite
I am receiving inaccurate date values in a VBA loop. The code I am using is below. I have a table of start dates and end dates some of which are February dates begining in "02". With all dates in the table that begin with "02" I am receiving string values that start with "12" with the remaining portion of the date the same. It looks as though there may be an error in the data type or something else. How can I define the dates as they are so that I may return accurate dates as they are written in the cells in the worksheet? Thanks!
Dim aa As Integer
Dim StartDate As String
Dim EndDate As String
Dim RngStart As Range
Dim RngEnd As Range
Dim RngStartR As String
Dim RngStartRng As Range
Dim RngEndR As String
Dim RngEndRng As Range
Dim RngXR As String: RngXR
ActiveWorkbook.Sheets("ActiveSheet").Range("C7").value
Dim RngXR2 As String: RngXR2 =
ActiveWorkbook.Sheets("ActiveSheet").Range("C8").value
Dim sh As Worksheet
Dim chrt As ChartObject
Dim ch As Chart
Dim zz As Integer
Dim NumObs2 As Long
NumObs2 = Sheets("AllDistanceMeasures").Cells(Rows.Count, 10).End(xlUp).Row
For aa = 5 To NumObs2
StartDate = Sheets("AllDistanceMeasures").Cells(aa, 9).value
EndDate = Sheets("AllDistanceMeasures").Cells(aa, 10).value
If StartDate <> "" Then
Set RngStart = Sheets("ActiveSheet").Cells.Find(What:=StartDate,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1)
Else
MsgBox "StartDate variable for " &
Sheets("AllDistanceMeasures").Cells(aa, 9).Address & " not found",
vbExclamation
Exit Sub
End If
RngStartR = RngStart.Address
If EndDate <> "" Then
Set RngEnd = Sheets("ActiveSheet").Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1)
Else
MsgBox "EndDate variable for " & Sheets("AllDistanceMeasures").Cells(aa, 10).Address & " not found", vbExclamation
Exit Sub
End If
RngEndR = RngEnd.Address
ActiveWorkbook.Sheets("LowDistCharts").Activate
Set sh = Worksheets("LowDistCharts")
Set chrt = sh.ChartObjects.Add(0, 0, 300, 300)
Set ch = chrt.Chart
Do While ch.SeriesCollection.Count > 1
ch.SeriesCollection(1).Delete
Loop
With chrt
.Height = 300
.Width = 300
.Top = 1 + ((aa - 4) * 300)
.Left = 1
End With
With ch
.HasTitle = True
.ChartTitle.Text = aa & " " & StartDate & " to " & EndDate
.ChartTitle.Font.Size = 8
.ChartType = xlLine
.SeriesCollection.NewSeries
.SeriesCollection(1).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngXR, RngXR2)
.SeriesCollection.NewSeries
.SeriesCollection(2).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStartR, RngEndR)
.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(3).Delete
.HasLegend = False
End With
For zz = 0 To NumObs - 1
Sheets("ActiveSheet").Range(RngEndR).Offset(zz, 0).Copy
Sheets("LowDistCharts").Cells(5, aa + 5).Offset(zz, 0).PasteSpecial xlPasteValues
Next zz
Next aa
excel vba
edited Nov 11 at 6:50
Cindy Meister
13.4k101934
asked Nov 11 at 4:32
Scott
1
2
You'll want to paste exactly the code that you're using. The code you pasted currently does not compile. Problem starting at lineDim RngXR As String: RngXR
– Marcucciboy2
Nov 11 at 5:25
If date is not correctly entered in excel, it will be either be saved as string, which has to be converted to date if any calculation is to be performed on it or it will be saved incorrectly i.e. month can be day or day can be month, even then it has to be converted first to correct date before doing something else. You have to write some code first to fix the dates as per what you have and what sort of conversion is required, after that you can run your original macro
– usmanhaq
Nov 11 at 7:44
you'll want to throw in an underscore there for it to compile properly. @usmanhaq I'll work with it to see if I can run bits of code before running the loop to affect format in the worksheet. thanks
– Scott
Nov 11 at 17:08
@usmanhaq could you elaborate on approaches to handling the dates? I've tried a few things: formatting columns, changing variable types, and use of CDate prior to setting values. None of these seem to be working and I haven't been able to find precedent online. If I can repeat back to you to make sure I am understanding correctly, I need to affect the data type at some point, either in the sheet or in the code and then run it. Is that what you are in essence saying?
– Scott
Nov 12 at 3:51
You can not fix the wrong dates using formatting or CDate, because it is a wrong entry. It depends upon how the users have entered the data, if you can show me some samples of how your dates are entered, i can recommend some solution.
– usmanhaq
Nov 12 at 7:58
|
show 1 more comment
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am receiving inaccurate date values in a VBA loop. The code I am using is below. I have a table of start dates and end dates some of which are February dates begining in "02". With all dates in the table that begin with "02" I am receiving string values that start with "12" with the remaining portion of the date the same. It looks as though there may be an error in the data type or something else. How can I define the dates as they are so that I may return accurate dates as they are written in the cells in the worksheet? Thanks!
Dim aa As Integer
Dim StartDate As String
Dim EndDate As String
Dim RngStart As Range
Dim RngEnd As Range
Dim RngStartR As String
Dim RngStartRng As Range
Dim RngEndR As String
Dim RngEndRng As Range
Dim RngXR As String: RngXR
ActiveWorkbook.Sheets("ActiveSheet").Range("C7").value
Dim RngXR2 As String: RngXR2 =
ActiveWorkbook.Sheets("ActiveSheet").Range("C8").value
Dim sh As Worksheet
Dim chrt As ChartObject
Dim ch As Chart
Dim zz As Integer
Dim NumObs2 As Long
NumObs2 = Sheets("AllDistanceMeasures").Cells(Rows.Count, 10).End(xlUp).Row
For aa = 5 To NumObs2
StartDate = Sheets("AllDistanceMeasures").Cells(aa, 9).value
EndDate = Sheets("AllDistanceMeasures").Cells(aa, 10).value
If StartDate <> "" Then
Set RngStart = Sheets("ActiveSheet").Cells.Find(What:=StartDate,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1)
Else
MsgBox "StartDate variable for " &
Sheets("AllDistanceMeasures").Cells(aa, 9).Address & " not found",
vbExclamation
Exit Sub
End If
RngStartR = RngStart.Address
If EndDate <> "" Then
Set RngEnd = Sheets("ActiveSheet").Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1)
Else
MsgBox "EndDate variable for " & Sheets("AllDistanceMeasures").Cells(aa, 10).Address & " not found", vbExclamation
Exit Sub
End If
RngEndR = RngEnd.Address
ActiveWorkbook.Sheets("LowDistCharts").Activate
Set sh = Worksheets("LowDistCharts")
Set chrt = sh.ChartObjects.Add(0, 0, 300, 300)
Set ch = chrt.Chart
Do While ch.SeriesCollection.Count > 1
ch.SeriesCollection(1).Delete
Loop
With chrt
.Height = 300
.Width = 300
.Top = 1 + ((aa - 4) * 300)
.Left = 1
End With
With ch
.HasTitle = True
.ChartTitle.Text = aa & " " & StartDate & " to " & EndDate
.ChartTitle.Font.Size = 8
.ChartType = xlLine
.SeriesCollection.NewSeries
.SeriesCollection(1).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngXR, RngXR2)
.SeriesCollection.NewSeries
.SeriesCollection(2).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStartR, RngEndR)
.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(3).Delete
.HasLegend = False
End With
For zz = 0 To NumObs - 1
Sheets("ActiveSheet").Range(RngEndR).Offset(zz, 0).Copy
Sheets("LowDistCharts").Cells(5, aa + 5).Offset(zz, 0).PasteSpecial xlPasteValues
Next zz
Next aa
excel vba
edited Nov 11 at 6:50
Cindy Meister
13.4k101934
asked Nov 11 at 4:32
Scott
1
I am receiving inaccurate date values in a VBA loop. The code I am using is below. I have a table of start dates and end dates some of which are February dates begining in "02". With all dates in the table that begin with "02" I am receiving string values that start with "12" with the remaining portion of the date the same. It looks as though there may be an error in the data type or something else. How can I define the dates as they are so that I may return accurate dates as they are written in the cells in the worksheet? Thanks!
Dim aa As Integer
Dim StartDate As String
Dim EndDate As String
Dim RngStart As Range
Dim RngEnd As Range
Dim RngStartR As String
Dim RngStartRng As Range
Dim RngEndR As String
Dim RngEndRng As Range
Dim RngXR As String: RngXR
ActiveWorkbook.Sheets("ActiveSheet").Range("C7").value
Dim RngXR2 As String: RngXR2 =
ActiveWorkbook.Sheets("ActiveSheet").Range("C8").value
Dim sh As Worksheet
Dim chrt As ChartObject
Dim ch As Chart
Dim zz As Integer
Dim NumObs2 As Long
NumObs2 = Sheets("AllDistanceMeasures").Cells(Rows.Count, 10).End(xlUp).Row
For aa = 5 To NumObs2
StartDate = Sheets("AllDistanceMeasures").Cells(aa, 9).value
EndDate = Sheets("AllDistanceMeasures").Cells(aa, 10).value
If StartDate <> "" Then
Set RngStart = Sheets("ActiveSheet").Cells.Find(What:=StartDate,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1)
Else
MsgBox "StartDate variable for " &
Sheets("AllDistanceMeasures").Cells(aa, 9).Address & " not found",
vbExclamation
Exit Sub
End If
RngStartR = RngStart.Address
If EndDate <> "" Then
Set RngEnd = Sheets("ActiveSheet").Cells.Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1)
Else
MsgBox "EndDate variable for " & Sheets("AllDistanceMeasures").Cells(aa, 10).Address & " not found", vbExclamation
Exit Sub
End If
RngEndR = RngEnd.Address
ActiveWorkbook.Sheets("LowDistCharts").Activate
Set sh = Worksheets("LowDistCharts")
Set chrt = sh.ChartObjects.Add(0, 0, 300, 300)
Set ch = chrt.Chart
Do While ch.SeriesCollection.Count > 1
ch.SeriesCollection(1).Delete
Loop
With chrt
.Height = 300
.Width = 300
.Top = 1 + ((aa - 4) * 300)
.Left = 1
End With
With ch
.HasTitle = True
.ChartTitle.Text = aa & " " & StartDate & " to " & EndDate
.ChartTitle.Font.Size = 8
.ChartType = xlLine
.SeriesCollection.NewSeries
.SeriesCollection(1).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngXR, RngXR2)
.SeriesCollection.NewSeries
.SeriesCollection(2).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStartR, RngEndR)
.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(3).Delete
.HasLegend = False
End With
For zz = 0 To NumObs - 1
Sheets("ActiveSheet").Range(RngEndR).Offset(zz, 0).Copy
Sheets("LowDistCharts").Cells(5, aa + 5).Offset(zz, 0).PasteSpecial xlPasteValues
Next zz
Next aa
excel vba
excel vba
edited Nov 11 at 6:50
Cindy Meister
13.4k101934
asked Nov 11 at 4:32
Scott
1
edited Nov 11 at 6:50
Cindy Meister
13.4k101934
asked Nov 11 at 4:32
Scott
1
edited Nov 11 at 6:50
Cindy Meister
13.4k101934
edited Nov 11 at 6:50
Cindy Meister
13.4k101934
edited Nov 11 at 6:50
Cindy Meister
13.4k101934
13.4k101934
asked Nov 11 at 4:32
Scott
1
asked Nov 11 at 4:32
Scott
1
asked Nov 11 at 4:32
Scott
1
1
2
You'll want to paste exactly the code that you're using. The code you pasted currently does not compile. Problem starting at lineDim RngXR As String: RngXR
– Marcucciboy2
Nov 11 at 5:25
If date is not correctly entered in excel, it will be either be saved as string, which has to be converted to date if any calculation is to be performed on it or it will be saved incorrectly i.e. month can be day or day can be month, even then it has to be converted first to correct date before doing something else. You have to write some code first to fix the dates as per what you have and what sort of conversion is required, after that you can run your original macro
– usmanhaq
Nov 11 at 7:44
you'll want to throw in an underscore there for it to compile properly. @usmanhaq I'll work with it to see if I can run bits of code before running the loop to affect format in the worksheet. thanks
– Scott
Nov 11 at 17:08
@usmanhaq could you elaborate on approaches to handling the dates? I've tried a few things: formatting columns, changing variable types, and use of CDate prior to setting values. None of these seem to be working and I haven't been able to find precedent online. If I can repeat back to you to make sure I am understanding correctly, I need to affect the data type at some point, either in the sheet or in the code and then run it. Is that what you are in essence saying?
– Scott
Nov 12 at 3:51
You can not fix the wrong dates using formatting or CDate, because it is a wrong entry. It depends upon how the users have entered the data, if you can show me some samples of how your dates are entered, i can recommend some solution.
– usmanhaq
Nov 12 at 7:58
|
show 1 more comment
2
You'll want to paste exactly the code that you're using. The code you pasted currently does not compile. Problem starting at lineDim RngXR As String: RngXR
– Marcucciboy2
Nov 11 at 5:25
If date is not correctly entered in excel, it will be either be saved as string, which has to be converted to date if any calculation is to be performed on it or it will be saved incorrectly i.e. month can be day or day can be month, even then it has to be converted first to correct date before doing something else. You have to write some code first to fix the dates as per what you have and what sort of conversion is required, after that you can run your original macro
– usmanhaq
Nov 11 at 7:44
you'll want to throw in an underscore there for it to compile properly. @usmanhaq I'll work with it to see if I can run bits of code before running the loop to affect format in the worksheet. thanks
– Scott
Nov 11 at 17:08
@usmanhaq could you elaborate on approaches to handling the dates? I've tried a few things: formatting columns, changing variable types, and use of CDate prior to setting values. None of these seem to be working and I haven't been able to find precedent online. If I can repeat back to you to make sure I am understanding correctly, I need to affect the data type at some point, either in the sheet or in the code and then run it. Is that what you are in essence saying?
– Scott
Nov 12 at 3:51
You can not fix the wrong dates using formatting or CDate, because it is a wrong entry. It depends upon how the users have entered the data, if you can show me some samples of how your dates are entered, i can recommend some solution.
– usmanhaq
Nov 12 at 7:58
2
2
You'll want to paste exactly the code that you're using. The code you pasted currently does not compile. Problem starting at line
Dim RngXR As String: RngXR– Marcucciboy2
Nov 11 at 5:25
You'll want to paste exactly the code that you're using. The code you pasted currently does not compile. Problem starting at line
Dim RngXR As String: RngXR– Marcucciboy2
Nov 11 at 5:25
If date is not correctly entered in excel, it will be either be saved as string, which has to be converted to date if any calculation is to be performed on it or it will be saved incorrectly i.e. month can be day or day can be month, even then it has to be converted first to correct date before doing something else. You have to write some code first to fix the dates as per what you have and what sort of conversion is required, after that you can run your original macro
– usmanhaq
Nov 11 at 7:44
If date is not correctly entered in excel, it will be either be saved as string, which has to be converted to date if any calculation is to be performed on it or it will be saved incorrectly i.e. month can be day or day can be month, even then it has to be converted first to correct date before doing something else. You have to write some code first to fix the dates as per what you have and what sort of conversion is required, after that you can run your original macro
– usmanhaq
Nov 11 at 7:44
you'll want to throw in an underscore there for it to compile properly. @usmanhaq I'll work with it to see if I can run bits of code before running the loop to affect format in the worksheet. thanks
– Scott
Nov 11 at 17:08
you'll want to throw in an underscore there for it to compile properly. @usmanhaq I'll work with it to see if I can run bits of code before running the loop to affect format in the worksheet. thanks
– Scott
Nov 11 at 17:08
@usmanhaq could you elaborate on approaches to handling the dates? I've tried a few things: formatting columns, changing variable types, and use of CDate prior to setting values. None of these seem to be working and I haven't been able to find precedent online. If I can repeat back to you to make sure I am understanding correctly, I need to affect the data type at some point, either in the sheet or in the code and then run it. Is that what you are in essence saying?
– Scott
Nov 12 at 3:51
@usmanhaq could you elaborate on approaches to handling the dates? I've tried a few things: formatting columns, changing variable types, and use of CDate prior to setting values. None of these seem to be working and I haven't been able to find precedent online. If I can repeat back to you to make sure I am understanding correctly, I need to affect the data type at some point, either in the sheet or in the code and then run it. Is that what you are in essence saying?
– Scott
Nov 12 at 3:51
You can not fix the wrong dates using formatting or CDate, because it is a wrong entry. It depends upon how the users have entered the data, if you can show me some samples of how your dates are entered, i can recommend some solution.
– usmanhaq
Nov 12 at 7:58
You can not fix the wrong dates using formatting or CDate, because it is a wrong entry. It depends upon how the users have entered the data, if you can show me some samples of how your dates are entered, i can recommend some solution.
– usmanhaq
Nov 12 at 7:58
|
show 1 more comment
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53245864%2fvba-find-method-setting-ranges-from-strings-inaccurately%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
You'll want to paste exactly the code that you're using. The code you pasted currently does not compile. Problem starting at line
Dim RngXR As String: RngXR– Marcucciboy2
Nov 11 at 5:25
If date is not correctly entered in excel, it will be either be saved as string, which has to be converted to date if any calculation is to be performed on it or it will be saved incorrectly i.e. month can be day or day can be month, even then it has to be converted first to correct date before doing something else. You have to write some code first to fix the dates as per what you have and what sort of conversion is required, after that you can run your original macro
– usmanhaq
Nov 11 at 7:44
you'll want to throw in an underscore there for it to compile properly. @usmanhaq I'll work with it to see if I can run bits of code before running the loop to affect format in the worksheet. thanks
– Scott
Nov 11 at 17:08
@usmanhaq could you elaborate on approaches to handling the dates? I've tried a few things: formatting columns, changing variable types, and use of CDate prior to setting values. None of these seem to be working and I haven't been able to find precedent online. If I can repeat back to you to make sure I am understanding correctly, I need to affect the data type at some point, either in the sheet or in the code and then run it. Is that what you are in essence saying?
– Scott
Nov 12 at 3:51
You can not fix the wrong dates using formatting or CDate, because it is a wrong entry. It depends upon how the users have entered the data, if you can show me some samples of how your dates are entered, i can recommend some solution.
– usmanhaq
Nov 12 at 7:58