VBA Posting Data from Userform to Table Error

silentJ

New Member
Joined
May 29, 2021
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
Hi

I am developing a project for work and I'm almost there but have just come stuck with one piece of code which I can not work out why its causing me a problem.
The userform has a command button which searches through rows of data to find the relevant line (search condition is on date/time in Column A), and when found it will load all textboxes with the data found in that record. This works fine.
The objective is to allow the user to then edit accordingly and press another command button to repost, overwriting the original row of data previously found. This is where the problem is, the code I am using is giving me "error 13 - type mismatch"
The full code is

VBA Code:
Private Sub CommandButton1_Click()
Dim srchrecord As String

srchrecord = Trim(dateTextBox.Value & ", " & timeTextBox.Value)
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Dim tbl As ListObject
If ActiveSheet.Name = "Kiln 1" Then
    Set tbl = Worksheets("Kiln 1").ListObjects("kiln1tbl")
End If
If ActiveSheet.Name = "Kiln 2" Then
    Set tbl = Worksheets("Kiln 2").ListObjects("kiln2tbl")
End If
If ActiveSheet.Name = "B3000" Then
    Set tbl = Worksheets("B3000").ListObjects("b3000tbl")
End If
If ActiveSheet.Name = "B5000" Then
    Set tbl = Worksheets("B5000").ListObjects("b5000tbl")
End If

For i = 2 To lastrow
If ActiveSheet.Cells(i, 1).Value = srchrecord Then
    [B]Cells(i, tbl.ListColumns("Kiln Temp").Range.Columns(1)).Value = tempTextBox.Value[/B]
End If
Next

End Sub

The offending line of code is in bold.
I have four worksheets with tables on each, and am using the same userform so I need to be able to distinguish between each table. Also, the columns on some of the sheets are different so I need to be able to locate the column by name, not by number.
Any help would be appreciated. Thankyou
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

silentJ

New Member
Joined
May 29, 2021
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
Ok, so its not shown it in bold but to be clear here is the offending line

VBA Code:
Cells(i, tbl.ListColumns("Kiln Temp").Range.Columns(1)).Value = tempTextBox.Value
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,019
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
Cells(i, tbl.ListColumns("Kiln Temp").Range.Column).Value = tempTextBox.Value
 
Solution

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,019
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback. :)
 

Forum statistics

Threads
1,136,300
Messages
5,674,961
Members
419,536
Latest member
Mohammed Jaffer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top