Code:
Dear Great Minds of Excel,
I have a user form on worksheet "PCODE8" that searches a worksheet called "StateInfo" to see if a county is listed in the column under the state abbreviation. the code successfully finds the correct State abbreviation, listed in the first cell in the column for that state, checks to see if the county is listed but fails to add the county if not listed. I have one column for each state and would like the columns to populate as users enter county names for the first time. The table shows worksheet "StateInfo" and the bold letter/numbers are just the column designation. They are not row 1, just for reference. I'm using the column number designations, 2-6, because I need to have the formula dynamic to find the state column to check if the county is listed then add if not listed.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][B]B (2)[/B][/TD]
[TD][B]C (3)[/B][/TD]
[TD][B]D (4)[/B][/TD]
[TD][B]E (5)[/B][/TD]
[TD][B] (6)[/B][/TD]
[/TR]
[TR]
[TD]AK[/TD]
[TD]AL[/TD]
[TD]AR[/TD]
[TD]AZ[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]County 1[/TD]
[TD]County 1[/TD]
[TD]County 1[/TD]
[TD]County 1[/TD]
[TD]County 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]County 2[/TD]
[TD]County 2[/TD]
[TD]County 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]County 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have successfully done this using an offset but the column has to be specifically designated and I need it to be dynamic. The code below is what I have and included are notes of where it goes into problems. Any suggestions would be outstanding help.
The two lines I'm struggling with are the following:
Range("I" & Rows.count).End(xlUp).Offset(1).Select '****this works
and I want to convert to
Range(.Cells(2, n), .Cells(1000, n)).End(xlUp).Offset(1).Select '**** this is what I'm trying to get to but the formula continues to replace (.Cells(2, n) instead of
'adding to next empty cell.
The entire section is below with notes.
'Check if County is in list ***********************************************
Dim stNme As String 'State Abbreviation in first cell of column
Dim ctyNme As String 'State's county name
stNme = cbxEntryState 'cbxEntryState is the userform field holding the State two letter abbreviation
ctyNme = cbxEntryCounty 'cbxEntryCounty is the userform field holding the county name
'Find Column With Target State Abbreviation
Dim Found As Range, LastRow As Long, n As Integer
Set Found = Sheets("StateInfo").Rows(1).Find(what:=stNme, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub
LastRow = Cells(Rows.count, Found.Column).End(xlUp).Row
n = Found.Column 'This is the column that matches the state's two letter abbreviation
'Select Range Of Target State to check if county is listed
Dim supMessage As String
supMessage = "This County is not listed. It will be added to the list."
With Sheets("StateInfo")
If WorksheetFunction.CountIf(.Range(.Cells(2, n), .Cells(1000, n)), ctyNme) = 0 Then
Application.Speech.Speak (supMessage)
'MsgBox "This Pest, I mean Officer, is not listed. It will be added to the list."
'''''''''''
Sheets("PCODE8").Range("AA1") = cbxEntryCounty
Sheets("PCODE8").Range("AA1").Select
Selection.Copy
'after copy go to destination page
Sheets("StateInfo").Visible = True
Sheets("StateInfo").Select
'
'Now paste to next empty row in column n between (2, n) and (1000, n) or use end(xlup)
'
Range("I" & Rows.count).End(xlUp).Offset(1).Select 'this works
'Range(.Cells(2, n), .Cells(1000, n)).End(xlUp).Offset(1).Select '**** this is what I'm trying to get to but the formula continues to replace (.Cells(2, n) only
'I have also tried the two below
'Range(.Cells(65536, n)).End(xlUp).Offset(1, 0).Select
'Range(.Cells(2, n), .Cells(1000, n)).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("StateInfo").Visible = False
Sheets("PCODE8").Select
'''''''''''''
End If
End With
I think I'm pretty close to an answer. I have searched through the forum and their are workable suggestions but cannot find how to make the column tied to a variable or make offset code dynamic. Any help is greatly appreciated.
Thank you.
Craig
