You are using the wrong function i.e. CInt should be CDate like so:
Option Explicit
Private Sub Txt_Depart_AfterUpdate()
Dim textYear As Date
Dim textYear2 As Date
textYear = CDate(Me.Txt_Arrival.Value)
textYear2 = CDate(Me.Txt_Depart.Value)
If textYear > textYear2 Then
MsgBox "Departure date is before arrival date"
Exit Sub
End If
End SubI believe you also had the Exit Sub in the wrong spot.
HTH
Robert
What was wrong with my solution here?
If you must cross-post please provide a link to each other site as I have done with the above link.
Thanks for letting us know and you're welcome 
Hi Pinecroft,
At a guess I'd say the issue is that the tab you originally run the code from has nothing in cell B8. When you run the code a second time the "VolunteerDistance" has been selected which has a value in cell B8 so all is good. Try moving this line of code...
val = Range("B8").Value...underneath this line to solve the issue:
Sheets("VolunteerDistance").SelectNote - you need to wrap any code you post within the appropriate tags as I have done like so:
[NOPARSE]
Your code here[/NOPARSE]
Regards,
Robert
Hi Johnnyv015,
Hard to know without having your workbook but the SaveAs syntax line looks correct. At a guess I'd say the FileSaveName variable is blank i.e. there's no text in cell R2 of the active sheet. If there is make sure the three variables that you've defined make a proper full path by putting them in a message box or the immediate window.
HTH
Robert
Hi jl2509,
Try this while on the sheet in question:
Option Explicit
Sub Macro1()
Dim blnCriteriaMet As Boolean
blnCriteriaMet = True
If Evaluate("COUNTIF($B$2:$B$50,""A"")") <= 9 Then
blnCriteriaMet = False
ElseIf Evaluate("COUNTIF($B$2:$B$50,""B"")") + Evaluate("COUNTIF($B$2:$B$50,""C"")") = 0 Then
blnCriteriaMet = False
End If
If blnCriteriaMet = False Then
MsgBox "Selections do not meeet requirements.", vbExclamation
Else
MsgBox "Selections Complete.", vbInformation
End If
End SubRegards,
Robert
QuoteI mean, when i change the data in 'Formula G&H', the Sheet1 AM raw is not updating automatically. It is only updating when I clear the data's in AM, then change the tabs.
Ah now I think I see what you mean. Try this (notice the new variable varMyArray which needs to contain every possible outcome the formula can produce):
Option Explicit
Private Sub Worksheet_Activate()
Dim lngLastRow As Long
Dim rngMyCell As Range
Dim varMyArray As Variant
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
varMyArray = Array("Rejected", "Not Required", "Y", "Approved") 'Along with blank cells, any cells from cell AM8 down which have this text will be overwritten. Change to suit.
'Populate each blank cell from cell AM8 with formula
lngLastRow = Sheets("Formula").Range("G:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'As both sheets start from Row 8 the 'lngLastRow' variable will be the same on Sheet1 as it is on the Formula sheet
If lngLastRow >= 8 Then
For Each rngMyCell In Range("AM8:AM" & lngLastRow)
If Len(rngMyCell) = 0 Or IsNumeric(Application.Match(rngMyCell, varMyArray, 0)) = True Then
With rngMyCell
.Formula = "=IF(OR(Formula!H" & rngMyCell.Row & ">0),""Rejected"",IF(Formula!I" & rngMyCell.Row & ">0,""Not Required"",IF(Formula!G" & rngMyCell.Row & ">0,""Y"",IF(SUM(Formula!G" & rngMyCell.Row & ":H" & rngMyCell.Row & ")=0,""Approved"",""""))))"
.Value = .Value 'Convert formula to values.
End With
End If
Next rngMyCell
End If
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End SubWhat's not working?? It is for me :confused:
Follow these four steps and let me know how you go:
1. Clear any data in Col AM of Sheet1 from cell AM8 down
2. Put some random data in Col. AM
3. Click any other tab and click back into Sheet1
4. Note how only blank cells will be populated not any you filled in from step 2
HTH
Robert
Try this worksheet event macro on Sheet1:
Option Explicit
Private Sub Worksheet_Activate()
Dim lngLastRow As Long
Dim rngMyCell As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Populate each blank cell from cell AM8 with formula
lngLastRow = Sheets("Formula").Range("G:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'As both sheets start from Row 8 the 'lngLastRow' variable will be the same on Sheet1 as it is on the Formula sheet
If lngLastRow >= 8 Then
For Each rngMyCell In Range("AM8:AM" & lngLastRow)
If Len(rngMyCell) = 0 Then
With rngMyCell
.Formula = "=IF(OR(Formula!H" & rngMyCell.Row & ">0),""Rejected"",IF(Formula!I" & rngMyCell.Row & ">0,""Not Required"",IF(Formula!G" & rngMyCell.Row & ">0,""Y"",IF(SUM(Formula!G" & rngMyCell.Row & ":H" & rngMyCell.Row & ")=0,""Approved"",""""))))"
.Value = .Value 'Convert formula to values. Comment out if you want the formula to remain.
End With
End If
Next rngMyCell
End If
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End SubQuoteAh Ha! very sneaky and unique way of doing things.
I wouldn't go that far as I've seen it quite a bit and used the idea myself. You could also have a single cell for each tab so everything is driven from the Map tab.
Glad we got it sorted.
Cheers,
Robert
Hi rinconpaul,
There is a named range called "From_Sheet" which has been set to cells A2:A9 of the Map tab. The code simply goes down this range and uses whatever is in Col. A to whatever is in Col. B i.e. Offset(0,1) per row to copy and paste respectively.
HTH
Robert
Hi Lenissa,
Not too sure what you mean as yes, each time Sheet1 is selected Col. AM is cleared and reset with with data from the Formula tab so any existing in that column will be cleared.
Sorry but I'm signing out of this post as I feel your original request has been met. If not I'm sure another Ozgrid member maybe able to solve your last request.
Regards,
Robert
QuoteI've inserted this code as new module only.
No, it has to go onto Sheet1 - refer my four points above :confused:
Have a look at the attached where when you open the file Col. AM is blank. Then click any other tab in the workbook and then click Sheet1 again. You then see the Col. AM is populated. This will always happen whenever you click back into Sheet1 from another sheet.
Thanks,
Robert
OK, automatically populating Col. AM of Sheet1 when it's selected (activated) is definitely the way to go then. Put this worksheet event macro* onto Sheet1:
Option Explicit
Private Sub Worksheet_Activate()
Dim lngLastRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Clear existing range
lngLastRow = Cells(Rows.Count, "AM").End(xlUp).Row
If lngLastRow >= 8 Then
Range("AM8:AM" & lngLastRow).ClearContents
End If
'Populate new range if applicabale
lngLastRow = Sheets("Formula").Range("G:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If lngLastRow >= 8 Then
With Range("AM8:AM" & lngLastRow) 'As both start from Row 8 the 'lngLastRow' variable is the same for both sheets
.Formula = "=IF(OR(Formula!H8>0),""Rejected"",IF(Formula!I8>0,""Not Required"",IF(Formula!G8>0,""Y"",IF(SUM(Formula!G8:H8)=0,""Approved"",""""))))"
.Value = .Value 'Convert formula to values. Comment out if you want the formulas to remain.
End With
End If
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End SubRegards,
Robert
*Note to install this macro follow these four steps:
Will the numbers in columns G and H be formula driven or manually keyed in? Why do you want to include changes in column I as well?
I think the best way is for Sheet1 to automatically updated each time it's selected.
What trigger do you want for the macro to run? Perhaps when Sheet1 is selected?
Hi Lenissa,
Here's some code putting your last formula into Sheet1:
Option Explicit
Sub Macro1()
Dim lngLastRow As Long
Application.ScreenUpdating = False
lngLastRow = Sheets("Formula").Range("G:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With Sheets("Sheet1").Range("AM8:AM" & lngLastRow) 'As both start from Row 8 the 'lngLastRow' variable is the same for both sheets
.Formula = "=IF(OR(Formula!H8>0),""Rejected"",IF(Formula!I8>0,""Not Required"",IF(Formula!G8>0,""Y"",IF(SUM(Formula!G8:H8)=0,""Approved"",""""))))"
.Value = .Value 'Convert formula to values. Comment out if you want the formulas to remain.
End With
Application.ScreenUpdating = True
End SubI see you've also changed the name of Sheet4 to Formula. Note you will have to change the macro if you change the sheet names going forward.
Regards,
Robert
Hi kamranyd,
Please start a new thread for each new question and make sure you wrap any code you post with the appropriate tags. I've now done it twice for you.
Thanks,
Robert
Hi Lenissa,
Welcome to Ozgrid!!
Could you please check if the following formula works as expected by putting it into cell AM8 of Sheet1 and copying it down to cell AM31:
=IF(OR(Sheet4!H8>0,SUM(Sheet4!G8:H8)>0),"Rejected",IF(Sheet4!G8>0,"Y",IF(SUM(Sheet4!G8:H8)=0,"Approved","")))
If it does I'll convert it to a macro for you. If not please amend it so it's correct and I'll convert that formula.
Thanks,
Robert
Hi Kman987,
You need to prefix the cells part of your code with the Current worksheet variable you've created like so:
Set range3 = [B]Current[/B].Cells.Find(What:="AAIDL00", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)Regards,
Robert