Posts by rollis13

    So that means that you need a static range for pasting. No need to use variable 'lr2':

    This could be a macro for you to be pasted in a standard module.

    As per your example, since your Dim Loc As String is inside the event Sub Worksheet_SelectionChange it will inizialize (to empty) everytime the event triggers. Move the Dim just before the Sub and your variable will retain the last input.


    If you put the Call inside the If/Then, and you are clicking outside the range, the Call will never execute; therefore A1 will not change.

    Here is where LocVar gets assigned:

    Code
    If Not Intersect(Target, Range("F9")) Is Nothing Then
        Range("A" & Sheets("VBA Data").Range("O35")).Select '<---- takes user where he needs to be
        LocVar = "AFR"
    End If

    but when you get to the Second Step, since your macro has skipped First Step because you aren't in cell F9, LocVar will not be assigned, that's why when you are in cells N103-N130 your variable will result empty.

    Code
    For j = 0 To 27                               '28 day planning horizon
        If Not Intersect(Target, Range("N" & 103 + j)) Is Nothing Then
            Dt_Req = Target.Value
            'Call Loc_Summary(LocVar, Dt_Req)
            MsgBox LocVar & " , " & Dt_Req        '<- - - - - - -now, the box shows ", 02/05/2022" What made LocVar disappear ?? Retriggering the event ?
        End If                                    '  ^-------------------------^
    Next

    You could work on something like this:

    Code
    For Each cell In Sheets("MySheet").Columns(17).Cells
        Select Case cell.Value
            Case "-29.75"
                If cell.Offset(0, -6) = "02-01-2022" Then
                    cell.Offset(0, 2).Value = "-8.33"
                ElseIf cell.Offset(0, -6) = "03-01-2022" Then
                    cell.Offset(0, 2).Value = "-10.50"
                End If
        End Select
    Next

    Have a try with my macro to be pasted in the sheet's module (the one with the dropdowns) and change the date format of the time-stamp if necessary:

    Code
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.EnableEvents = False
        If (Target.Column = 6 Or Target.Column = 8 Or Target.Column = 10) Then Target.Offset(0, 1) = Format(Now, "mm/dd/yyyy hh:mm:ss")
        Application.EnableEvents = True
    End Sub

    Yes that's right; as you may understand, the data is too long or too wide to fit in one page, you must decide, or reduce the hight of the rows or reduce the width of the columns in your sheet. Then you can go back to:

    Code
    .FitToPagesWide = 1
    .FitToPagesTall = 1