Closed Thread
Results 1 to 2 of 2

VBA Code does recognise value in a cell if value is provided by formula.

  1. #1
    Registered User
    Join Date
    06-17-2018
    Location
    Coomealla, Australia
    MS-Off Ver
    Office 365
    Posts
    10

    VBA Code does recognise value in a cell if value is provided by formula.

    Hi All,
    its been along time since I visited this forum. I need some assistance please.

    I have a form that inputs data into a worksheet and it also allows me to change the data in any record (line of data) l select. This is done using a combo box on the form selecting a unique number that I have given each record. The column where the unique number is stored comes from another worksheet in the same workbook which is a obtained by a formula.

    When I use the combo box to select the specific unique number I require they all show up and I can select the unique number that I want and all data associated with that number is visible and can be changed.

    When I press the submit button on the form I get the Run-time error '91' : object variable or with block not set

    If I was to manually type the unique number into the column where they are stored and use the form it works perfectly. So I believe the code is not recognizing the value that the formula is inputting as a valid piece of data. I think!

    thank you.

    Here is the code. I hope I am doing this correctly.

    Private Sub cmdSubmitData_click()
    Dim Fnd As Range
    If MsgBox("Are you sure you want to update the records?", vbYesNo + vbQuestion, "update Record") = vbYes Then
    Set Fnd = Range("B:B").Find(Me.cmbTruckDockets.Value, , , xlWhole, , , False, , False)
    Cells(Fnd.Row, 12) = txtNetWeight.Value
    Cells(Fnd.Row, 13) = txtRate.Value
    Cells(Fnd.Row, 17) = txtHarvestPay.Value
    Cells(Fnd.Row, 18) = txtHarvestPayDate.Value
    Cells(Fnd.Row, 19) = txt2ndPayment.Value
    Cells(Fnd.Row, 20) = txt2ndPayDate.Value
    Cells(Fnd.Row, 25) = txtLoyaltyLarge.Value
    Cells(Fnd.Row, 26) = txtLoyaltyMedium.Value
    Cells(Fnd.Row, 27) = txtLoyaltyFreight.Value
    Cells(Fnd.Row, 32) = txtKCT1stGradePay.Value
    Cells(Fnd.Row, 33) = txtKCT2ndGradePay.Value
    Cells(Fnd.Row, 34) = txt1stGradePay.Value
    Cells(Fnd.Row, 35) = txt2ndGradePay.Value
    Cells(Fnd.Row, 36) = txt3rdGradePay1.Value
    Cells(Fnd.Row, 37) = txtFactoryPay.Value
    Cells(Fnd.Row, 50) = txtKCT1stGradeWeight.Value
    Cells(Fnd.Row, 51) = txtKCT2ndGradeWeight.Value
    Cells(Fnd.Row, 52) = txt1stGradeWeight.Value
    Cells(Fnd.Row, 53) = txt2ndGradeWeight.Value
    Cells(Fnd.Row, 54) = txt3rdGradeWeight.Value
    Cells(Fnd.Row, 55) = txtFactoryWeight.Value
    Cells(Fnd.Row, 56) = txtLoyaltyCartonLarge.Value
    Cells(Fnd.Row, 57) = txtLoyaltyCartonMedium.Value
    Cells(Fnd.Row, 65) = txtRemarks.Value
    Cells(Fnd.Row, 73) = Blemish.Value
    Cells(Fnd.Row, 74) = Albedo.Value
    Cells(Fnd.Row, 75) = Small_Sizes.Value
    Cells(Fnd.Row, 76) = Necking.Value
    Cells(Fnd.Row, 77) = Some_Green.Value
    Cells(Fnd.Row, 78) = Coarse.Value
    Cells(Fnd.Row, 79) = Large_Sizes.Value
    Cells(Fnd.Row, 80) = Sunburn.Value
    Cells(Fnd.Row, 81) = Splits.Value
    Cells(Fnd.Row, 82) = Heavy_Blemish.Value
    Cells(Fnd.Row, 83) = Scales.Value
    Cells(Fnd.Row, 84) = Dry.Value
    Cells(Fnd.Row, 85) = Puffy.Value
    Cells(Fnd.Row, 86) = Katydid.Value
    Cells(Fnd.Row, 87) = Oval_Shaped.Value
    End If
    End Sub

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,120

    Re: VBA Code does recognise value in a cell if value is provided by formula.

    Administrative Note:

    Welcome to the forum.

    Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here. I am, therefore, closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-pro...y-formula.html
    Last edited by AliGW; 06-18-2019 at 02:52 AM.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Zillow API Extraction - Where to Start?! (Code already provided)
    By cmanlongat in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-01-2016, 01:40 PM
  2. Replies: 5
    Last Post: 09-14-2015, 03:29 AM
  3. [SOLVED] Formula in one cell that picks up another based on dates provided in the same sheet
    By s.tara91 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-16-2013, 12:58 AM
  4. Formula to recognise one word from multiple words in the same cell
    By JLucy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-27-2013, 11:48 AM
  5. Replies: 3
    Last Post: 01-11-2012, 08:17 AM
  6. Locking a cell that contains a formula that has provided a result
    By redders in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-16-2010, 05:14 AM
  7. [SOLVED] How do I write a formula to color code based on dates provided or.
    By jaime in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2005, 10:06 AM

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