Table editing

[Newton/sqm]

Good day


I have the following problem:


I have a list of sample ID's in a table and the user wants to edit the entries for each ID whenever he needs to. So lets say for example that one ID is A0225. The user wants to edit the column "Yield Strenght" from his UserForm. He clicks on the button edit table and the column "Yield Strength" is updated.


My code behind the userform looks like this at the moment:

Dim lstObj As ListObject
Dim objNewRow As ListRow
Dim i As Integer
Dim ID As ListObject
Dim tbl As ListObject
Dim tRows As Long
Dim tCols As Long
Dim ptr As LongPtr

Set Obj = Worksheets("Tests Results").ListObjects("Test_results")

'count rows
Set tbl = Worksheets("Tests Results").ListObjects("Test_results")
With tbl.DataBodyRange
tRows = .Rows.Count
End With

For i = 1 To tRows
If obj = Me.SampleID.value Then
Set objNewRow = lstObj.ListRows.Change(AlwaysInsert:=True)

With Obj
.ListColumns("Yield Strength").DataBodyRange(x) = Me.Yield.Value
end with
end if

[SamT]

The first error I see is

For i = 1 To tRows
If obj = Me.SampleID.value Then
where the obj Variable is not assigned a value before testing its value.

[Aussiebear]

@Sam Sorry to intrude but.... is the line Set tbl etc necessary?

[SamT]

Your always welcome. I appreciate it when someone adds to the conversation. It keeps me honest. :D

I do see now where I made a mistake about the error. obj has been initialized. It is not explicitly Dimmed so it's a Variant, but it is set to the same thing as the ListObject Variable tbl. Whatever obj is, it is not a String = Me.SampleID.value


obj is used as a ListObject and as a String value.
lstObj is not set

As to your question, I would replace all instances of "obj" with "tbl" and lose the "Set obj =" line. He certainly doesn't need both, and "tbl" is a better mnemonic than "obj."

I bet Newton's eye would not have let

If tbl = Me.SampleID.value pass.

[Aussiebear]

True, but since I didn't get that far into the code... I'll accept your guidance

Actually there is one other item which has jumped out, namely "Dim ptr as Longptr" I'm guessing its a typo as its a variant that I've never heard of before. Since ptr could be anything at this point and is not yet used within the code sample, perhaps there is more afoot dear Watson.

[SamT]

I've only seen it in non VBA code. LongPtr for Long (memory) Pointer. Don't ask which language it was in.

[Newton/sqm]

I used the ptr only because I am used to use pointers in C. But this does not apply in VBA. Can I rephrase my question? How do you write a for loop that loops through the first column of a table. If the value in a cell equals a certain sampleID, then edit the row with data optained from the userform. else display "The ID does not exsist.". My problem really is that I do not know how to read the value in a cell. In C, you have a pointer that loops through an array. If the value of the arrayelement, that the pointer is pointing at, then do certain stuff.

[snb]

Assuming "Yield Strength" is he 4th column:

Sub M_snb()
sheets("Tests Results").ListObjects("Test_results").columns(1).find("A0225",,1).offset(,3)="lethal strength"
End Sub

Although you can loop through column 1 it's not necessary.

@SamT

LongPtr in VBA 64 bit version (especially when using 64 bit API's).

[Newton/sqm]

So the code looks like this now: (I am looking for the ID in the first column to edit its entire row)

ID = SampleID.Value
Set Obj = Worksheets("Tests Results").ListObjects("Test_results").Columns.Find("ID",,1).Offset(, 0)
Set objNewRow = Obj.ListRows(AlwaysInsert:=True)
y = objNewRow.Index
With Obj
.ListColumns("Test Lab").DataBodyRange(x) = Me.ComboBox_LAB.Value
.ListColumns("Flammability Type ").DataBodyRange(x) = Me.ComboBoxFlamm.Value
.ListColumns("Avg-Smoke Density Pass Value (Ds)").DataBodyRange(x) = Me.ComboBoxSDpass.Value

End With
End Sub

I get the error :438 "Object does not support this property or method" in line 2

[snb]

"ID" <>ID



sheets("Tests Results").ListObjects("Test_results").range.Columns(1).Find(SampleID.Value,,1).Offset(, 3).resize(,3)=array(ComboBox_LAB.Value,ComboBoxFlamm.Value ,ComboBoxSDpass.Value)
No need to create object variables.

[SamT]

@ snb,
Nah, I was thinking back in the 70s to late 80's. 8 bit systems.

[SamT]

snb gives c programmers lesson on terse, succinct coding. :D

[snb]

@SamT

I forgot all about Fortran IV :banghead: