using Resize to define range for updating a COLUMN from userform

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
476
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

Code:
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)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
If r is a column number then cells(2,r) will give you the 2nd row in that col.
 
Upvote 0
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.

Code:
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.
:confused:)

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 :)
 
Last edited:
Upvote 0

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.)
:confused:

 
Last edited:
Upvote 0
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?
 
Upvote 0
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:

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)
 
Upvote 0
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?
 
Upvote 0
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:

Code:
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:
Code:
r = Application.WorksheetFunction.Match(namID, DynRng.Resize[COLOR=#000080][B](, 1), 1)[/B][/COLOR]
 
Upvote 0
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:)
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.
 
Last edited:
Upvote 0
also, the message box for my NamID says "Allison, Chris" (which is also correct)

Code:
MsgBox namID
MsgBox r
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top