Hello, All!
I have a userform and am attempting to search through an array, see if something (the url) is there, if it isn't there then add a number (or other tally mark I can remove later) to it's neighbor, but if it is there just recreate the entry that is already there and let the neighbor stay as is.
Here's the workbook: Source and Animal.xlsm, no colors or other formatting needs to be maintained.
I'm wondering if maybe stringtofind is looking for my value as a portion of string versus as an exact match. Also not sure of the best way to set up a loop for my y, though this method of checking if the radio button was checked worked great and was trying to gain inspiration from that. Had at one point just had it add a "|" as a tally so I could just replace those later, so I think that could be a workable other approach. It was at least working moreorless and then in removing identifying features by making a new mini userform, lost some functionality, but if anyone could just lead me in the right direction to another concept or even another post that that would be appreciated.
Thank you!
Option Explicit
Public Function IsInArray2DIndex(stringToFind As String, arr As Variant) As Variant
IsInArray2DIndex = Array(-1, -1)
Dim i As Long
Dim j As Long
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
If arr(i, j) = stringToFind Then
IsInArray2DIndex = Array(i, j)
Exit Function
End If
Next j
Next i
End Function
Private Sub ArrayTest()
''check radio selected
' Dim cnt As Integer
' Dim ctl As Control
'
' For Each ctl In Me.Controls
' If TypeName(ctl) = "OptionButton" Then
' If ctl.Value = True Then cnt = cnt + 1
'
' End If
' Next ctl
' If cnt = 0 Then MsgBox "Source is required"
' If cnt = 0 Then Exit Sub
Dim ListRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ListRow = ws.Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Row
With ws
If OptionButton1.Value = True Then
.Range("C1").Value = "Google"
End If
If OptionButton2.Value = True Then
.Range("C1").Value = "Bing"
End If
If OptionButton3.Value = True Then
.Range("C1").Value = "Yahoo"
End If
If OptionButton4.Value = True Then
.Range("C1").Value = "Intranet"
End If
Dim SourceCol As Variant
Dim URLCol As Variant
URLCol = Sheets("Sheet1").Range("b1:b100")
SourceCol = Sheets("Sheet1").Range("c1:c100")
Dim CurrURL As String
CurrURL = Me.RelChemsURLTextBox.Value
Dim CurrSource As String
CurrSource = Sheets("Sheet1").Range("C1").Value
Dim resultURL As Variant
Dim resultSrc As Variant
resultURL = IsInArray2DIndex(CurrURL, URLCol)
resultSrc = IsInArray2DIndex(CurrSource, SourceCol)
Dim iRow As Variant
Dim iCol As Variant
iRow = resultURL(0)
iCol = resultURL(1)
MsgBox "resultURL(0) is " & resultURL(0)
Sheets("Sheet1").Range("C2").ClearContents
Dim y As Integer
y = 1
If resultSrc(1) >= 0 And resultURL(1) >= 0 Then 'if source AND urls are NOT new entries in cols and already exist
MsgBox Sheets("Sheet1").Cells(iRow, iCol).Value & " is in column at row: " & iRow
Sheets("Sheet1").Range("C2").Value = Sheets("Sheet1").Cells(iRow, iCol).Value
ElseIf Not resultSrc(1) >= 0 Then 'if source IS in column
Sheets("Sheet1").Range("C2").Value = Sheets("Sheet1").Range("C1").Value & y
y = y + 1
End If
.Cells(ListRow, 4).Value = Me.TextBox1URL.Value
.Cells(ListRow, 5).Value = .Range("C2").Value
.Cells(ListRow, 6).Value = Me.TextBox2Product.Value
End With
Unload Me
End Sub
Luar biasa! Terima kasih! Oh, how much I appreciate you.