+ Reply to Thread
Results 1 to 3 of 3

VB code to find row number based on text not working

  1. #1
    Registered User
    Join Date
    02-23-2021
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2

    VB code to find row number based on text not working

    Hello guys, I would really appreciate some help.

    I am trying to make a dynamic calendar for work on excel, I have built most of it but am stuck on this.

    I have added all my colleagues names on a separate sheet to column A, with the view to log their annual leave, study leave etc on this in a row format. I have constructed the below VB macro code to input the dates into the spreadsheet via a user form I made in VBA project, however when the code runs I receive the "Name not found" error message even though the name is definitely in the A:A column.

    When I look into the debug "Sheet1.Cells(rownumber, Lstart).Value = leave" is highlighted in yellow and within this rownumber shows as "empty".

    Any ideas why this is happening? Thank you

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Private Sub enterleave_Click()

    Dim name As String
    Dim leave As String
    Dim rng As Range
    Dim rownumber, monthmove As Integer
    Dim Lstart, LEnd, difference, lnext As Integer

    Dim x As Long

    If TextBox1.Text = "" Then
    MsgBox "Enter Name"
    End If
    'name = TextBox1

    name = Trim(TextBox1.Text)
    Set rng = Sheet1.Columns("A:A").Find(What:=name, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If rng Is Nothing Then
    MsgBox "Name not found"
    Else
    rownumber = rng.Row

    End If
    If DropBox1.Value = "Annual" Then
    leave = "AL"
    End If
    If DropBox1.Value = "Study" Then
    leave = "SL"
    End If
    If DropBox1.Value = "Post on call" Then
    leave = "POC"
    End If
    If DropBox1.Value = "Zero day" Then
    leave = "ZeroDay"
    End If
    If DropBox1.Value = "OFF" Then
    leave = "OFF"
    End If


    If DropBox2.Value = "January" Then
    monthmove = 1
    End If
    If DropBox2.Value = "February" Then
    monthmove = 32
    End If

    If DropBox2.Value = "March" Then
    monthmove = 60
    End If

    If DropBox2.Value = "April" Then
    monthmove = 91
    End If

    If DropBox2.Value = "May" Then
    monthmove = 121
    End If

    If DropBox2.Value = "June" Then
    monthmove = 152
    End If

    If DropBox2.Value = "July" Then
    monthmove = 182
    End If

    If DropBox2.Value = "August" Then
    monthmove = 213
    End If

    If DropBox2.Value = "September" Then
    monthmove = 244
    End If

    If DropBox2.Value = "October" Then
    monthmove = 274
    End If

    If DropBox2.Value = "November" Then
    monthmove = 305
    End If

    If DropBox2.Value = "December" Then
    monthmove = 335
    End If

    If DropBox2.Value = -1 Then
    MsgBox "No month Selected"
    End If
    'MsgBox monthmove

    Lstart = Trim(TextBox2.Text) + monthmove
    LEnd = Trim(TextBox3.Text) + monthmove

    Sheet1.Cells(rownumber, Lstart).Value = leave
    Sheet1.Cells(rownumber, LEnd).Value = leave
    difference = LEnd - Lstart
    If difference > 1 Then
    lnext = Lstart
    Do While lnext < LEnd
    Sheet1.Cells(rownumber, lnext).Value = leave
    lnext = lnext + 1
    If lnext = LEnd Then
    GoTo ende
    End If
    Loop
    End If

    ende:

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,359

    Re: VB code to find row number based on text not working

    Hi Nightowel and welcome to the forum,

    When you say it "name is definitely" in column A, did you check for leading or trailing or double spaces in the name? If you have those extra spaces in the search it won't find it and turn up empty.
    Step through your code and add a Watch Window to see what that rownumber is. I also stop my macro many times and do a debug.print of variables in the Immediate window to check.
    Hope this helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    02-23-2021
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2

    Re: VB code to find row number based on text not working

    Hi thanks for your reply

    I managed to fix the problem, the sheet number was set incorrectly. It should have been Sheet3.Cells.

    It is working such that the annual leave is now being added to the correct persons row based on their name.

    However the dates are off by 4 days as this part of the code is for some reason not being incorporated. No debug error is shown.

    Lstart = Trim(TextBox2.Text) + monthmove
    LEnd = Trim(TextBox3.Text) + monthmove

    In other words, the data is added to the column number based on the number in TextBox2.Text and TextBox3.Text. It is not adding the monthmove component, which from my previous post I have changed to the following.

    If DropBox2.Value = "January" Then
    monthmove = 4
    End If
    If DropBox2.Value = "February" Then
    monthmove = 35
    End If

    And so forth.

    Any ideas why it is not adding the monthmove bit?

    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Can't find VBA code to format a column based on text within a row
    By LuciferUntamed in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-21-2020, 01:30 AM
  2. [SOLVED] VB Code to Copy certain text based on number of data
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-24-2019, 03:04 PM
  3. VBA code to find the row number based on data via input box and the highlight a cell
    By Aditya Sabat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2014, 05:47 PM
  4. Replies: 3
    Last Post: 10-24-2012, 07:11 AM
  5. code to find text, offset 1 column and paste to new workbook not working
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-10-2011, 07:55 AM
  6. Replies: 7
    Last Post: 03-03-2008, 11:48 AM
  7. Find column number based on text
    By pikapika13 in forum Excel General
    Replies: 1
    Last Post: 05-09-2005, 05:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1