0

I have two sheets first sheet gets data from SQL table so the range always changes depending on data size I have determined what the range is for cell A2: endxldown

In sheet 2 I have a date form which promts user to enter a date when user enters the date e.g. today, excel needs to enter this date in sheet 2 e.g. sheet 1 has data range of A2:A20 so sheet 2 will enter todays dats in range A2:A20

This gives me range in sheet 1;

Sub findlastrow()
Dim LastRow As Long

With Worksheets("Data").Activate
LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
Range("A1").Resize(LastRow).Select
MsgBox "The data range address is " & Selection.Address(0, 0) & ".", _
vbInformation, "Data-Contatining range address:"
End With


End Sub

Answer = A2:A251

The below code is suppose to enter date in range A2:A251

Sub CreateJnl()

Dim dte As String
Dim LastRow As Long
Dim rng As Range
Dim ws As Worksheet




Application.Calculation = xlManual
Application.ScreenUpdating = False

Sheets("Journal").Cells.ClearContents

Set ws = ThisWorkbook.Sheets("Data")

With ws
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
Range("A2").Resize(LastRow).Select
MsgBox "The data range address is " & Selection.Address(0, 0) & ".", _
vbInformation, "Data-Contatining range address:"

    Else
        LastRow = 1
    End If

    If Not LastRow < 3 Then
        Set rng = .Range("A2:A" & LastRow)

        Debug.Print rng.Address
    Else
        MsgBox "No Data found beyond A3"
    End If
End With



dte = InputBox("Please Enter Date: ", Default:=Format(Now, "dd/mm/yyyy"))
Sheets("Journal").rng.Value = dte


Application.ScreenUpdating = True
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Reg Chand
  • 57
  • 7
  • 1
    What's the problem/question here? Here `LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row` your `Cells` is not scoped to the `With` block, and you should check you got a hit before trying to access the `Row` property – Tim Williams Nov 06 '19 at 22:40
  • Side note: you [don't need to activate and select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – cybernetic.nomad Nov 06 '19 at 22:42
  • how do I get range from "data" sheet where range is "A2:A250" and enter it in Sheet Journal e.g. if I enter date Sheets("Journal").rng.Value = dte instead of rng if I enter A2:A10" it works. – Reg Chand Nov 06 '19 at 22:48

1 Answers1

1

the rng variable isn't being set on the Journal worksheet, so you can fill it that way. If you make the range name a variable, it can be used in the statement

Sub CreateJnl()

    Dim dte As String, strRange As String
    Dim LastRow As Long
    Dim rng As Range
    Dim ws As Worksheet

    Application.Calculation = xlManual
    Application.ScreenUpdating = False

    Sheets("Journal").Cells.ClearContents

    Set ws = ThisWorkbook.Sheets("Data")

    With ws
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows).Row
            Range("A2").Resize(LastRow).Select
            MsgBox "The data range address is " & Selection.Address(0, 0) & ".", _
            vbInformation, "Data-Contatining range address:"
        Else
            LastRow = 1
        End If

        If Not LastRow < 3 Then
            'Set rng = .Range("A2:A" & LastRow)
            strRange = "A2:A" & LastRow
            'Debug.Print rng.Address
        Else
            MsgBox "No Data found beyond A3"
        End If
    End With

    dte = InputBox("Please Enter Date: ", Default:=Format(Now, "dd/mm/yyyy"))
    Sheets("Journal").Activate
    Sheets("Journal").Range(strRange).Value = dte

    Application.ScreenUpdating = True

End Sub
James
  • 68
  • 8