using Resize to define range for updating a COLUMN from userform

[kbishop94]

I have code that I regularly use to update a ROW source for amending data that is in a row (going across), but I cannot figure out how to do the same thing but from a column.

Private Sub cmdAdd1_Click()

Dim DynRng As Range, Inc_ID As String, r As Long
Set DynRng = Worksheets("Seatex Incident Log").Range("DynamicRange")

Inc_ID = txtIncidentID1

r = Application.WorksheetFunction.Match(Inc_ID, DynRng.Resize(, 1), 0)

DynRng.Cells(r, [COLOR=#000080][B]2[/B][/COLOR]).Value = Me.DTPicker3.Value
DynRng.Cells(r, [COLOR=#000080][B]4[/B][/COLOR]).Value = Me.cboPriority1.Value
DynRng.Cells(r, [COLOR=#000080][B]18[/B][/COLOR]).Value = Me.chkCAPA1.Value
DynRng.Cells(r, [COLOR=#000080][B]6[/B][/COLOR]).Value = Me.cboLocation1.Value

("Inc_ID" is located somewhere in column A)
Where: (r, 2), (r, 4), (r, 18), or (r, 6) are all cells located 2 columns over from "Inc_ID" , or 4 columns over, or 18 columns over... etc...

But... what if 'Inc_ID' is located somewhere in row 1 (in some column) and I want to go DOWN from that specific cell. How do i define my new range for going down rows instead of going across columns like the above code does? (sure hope that makes sense... thank you)

 

[Fluff]

If r is a column number then cells(2,r) will give you the 2nd row in that col.

 

[kbishop94]

If r is a column numberthen cells(2,r) will give you the 2nd row in that col.

Well, I have done something screwy (again) and not sure why its selecting the cell that its selecting (as 'r')

"DyamicRange" (range used in my code in post) in my example starts at row 18(col A) , and goes over the the last column (Y) and goes down the last row (currently 433rd row)

With the code below, and on my userform, I am entering for 'txtIncidentID1' "18-416". 18-416 is located in column A, and is in the 430th row.

Private Sub cmdAdd1_Click()

Dim DynRng As Range, Inc_ID As String, r As Long
Set DynRng = Worksheets("Seatex IncidentLog").Range("DynamicRange")

Inc_ID = txtIncidentID1

r = Application.WorksheetFunction.Match(Inc_ID,DynRng.Resize(, 1), 0)

DynRng.Cells(2, r).Value = Me.txtNotes1.Value

MsgBox Inc_ID
MsgBox r

End Sub

My 1st message box in my code says (after clicking the 'Add1'commandbutton) that Inc_ID is: 18-416 (which is correct... thats what I entered on the userform...so far so good...)

But the 2nd message box for 'r' says: 413.

413 just happens to be the row that 18-416 is in when you subtract 17 rows from it (since my dynamic range starts in row 18.) 430-17 =413.

Furthermore, its placing my 'test text' (field called 'txtNotes1'on my userform that I am using to enter something unique and then after the sheet us updated, I search for that string and see where it put it. It put it in the same row as 18-416, but waaaay over in column 'OW' (which happens to be the 413th column. )

Here is where my 'test text' i entered for txtIncidentID1 on my userform ends ends up: (I entered SEATEX77 and the code placed it in column OW and 2 rows below the first row of my DynamicRange; row 19)



Thanks for any help

 

[kbishop94]

Here is where my entry for 'txtIncidentID1' in the userform is for example (I entered 18-416)
18-416 is located in row 430 and in column A. (again, my goal here is to go DOWN from txtIncidentID1 2 columns. Not across from it as whatever i seem to try its doing.)

 

[Fluff]

You cannot go down 2 columns, you either go down 2 rows, or across 2 columns.
What value are you trying to extract from the above image?

 

[kbishop94]

You cannot go down 2 columns, you either go down 2 rows, or across 2 columns.
What value are you trying to extract from the above image?

Thank you, Fluff. Thanks for the reply & help.

Well, that was what I was afraid of (I cant go down in a column from my 'source' cell.)

The above code and screen shots I posted yesterday are from a different workbook that I use the same code for. I wasn't able to upload any images yesterday, so I had to use some images I already had links to... another workbook, but using the same code I used for updating a record. so sorry about any confusion that might of caused.

Here is what I am attempting to do:

Here is my worksheet showing my 'source' cell (which in the code below and for my example here is going to be: "Allison, Chris" = 'namID' )



And my current code:

Private Sub cmdUPDATE_Click()
Dim ws As Worksheet

Set ws = Worksheets("Employee Training Matrix")
Worksheets("Employee Training Matrix").Activate

Dim DynRng As Range, namID As String, r As Long
Set DynRng = Worksheets("Employee Training Matrix").Range("DynamicRange")
namID = cboEN

r = Application.WorksheetFunction.Match(namID, DynRng.Resize(, 1), 1)

DynRng.Cells(2, r).Value = Me.txtOther1.Value

MsgBox namID
MsgBox r

End Sub

When the userform opens, the user chooses a name (again, I chose 'Allison, Chris' for demonstration purposes)



After moving away from that combobox and going to the next field after a name is selected, it populates the right side of the userform with the data from the worksheet correlating with the name "Allison, Chris"


What i was hoping I would have been able to do is to make changes for that name using the userform checkboxes you see above, and then amend that data back onto the worksheet in the rows located BELOW the name (which for my example would be cells in rows 2 through 7 shown below)

 

[Fluff]

Well, that was what I was afraid of (I cant go down in a column from my 'source' cell.)

You can go down IN a column, but you cannot go down 2 columns.
What is not working with your code?

 

[kbishop94]

You can go down IN a column, but you cannot go down 2 columns.
What is not working with your code?

ok, well thats good news, then.

If I run the code like this:

r = Application.WorksheetFunction.Match(namID, DynRng.Resize[COLOR=#000080][B](, 1), 0)[/B][/COLOR]
DynRng.Cells(2, r).Value = Me.txtOther1.Value

then I get this:



When i change the code to this:

r = Application.WorksheetFunction.Match(namID, DynRng.Resize[COLOR=#000080][B](, 1), 1)[/B][/COLOR]

 

[kbishop94]

then it works, and for my test entry to see where its placing the updated data, I entered "SEATEX99". And it ended up placing it here:



YELLOW highlighted column is the column where I want the data to go (not even worried about which cell in that column quite yet... I'm just trying to get it to go to the correct column FIRST... I'll worry about which cell once i get that figured out lol )

Green is the data i entered on the userform and where the code placed the data on the worksheet.

my message box for 'r' also says 4. (& its 4 no matter what I seem to change for this code

r = Application.WorksheetFunction.Match(namID, DynRng.Resize[COLOR=#000080][B](, 1), 1)[/B][/COLOR]

btw, the cell it placed it in is the 4th cell over from the start of my dynamic range (DynamicRange) Im assuming thats the 4 its assigning to 'r'.

edit again: specifically it;s the 4th cell over and 2nd cell down.

 

[kbishop94]

also, the message box for my NamID says "Allison, Chris" (which is also correct)

MsgBox namID
MsgBox r