Need help with find and replace - but i think it might be a bit more complicated than just that :(

[robertmwaring2]

Hello,

Let me first start by saying I am still VERY new to VBA. I use formulas on sheets as a helper in various cells on different sheets to assist me when I do not have the knowledge/skill to accomplish it in VBA alone. That being said, here is my dilemma:

In my workbook I have 2 forms (Form1 and Form2 for arguments sake). When the value of the combobox on Form1 is a particular value it calls Form2.

Form2 has another combobox. I am trying to take the value of that combox and place it on a sheet within the workbook. My issue is, it could be any number of sheets, in any number of columns, at any row number. Because of this I have created helper cells.

On a “Master Info” sheet in the same workbook, I have the following:

V1=value of Form1 combobox selection (is entered on combobox change via VBA)

V2=formula that concatenates the sheet name that V1 is located in to be used in other formulas (turns name from Sheet 1 to ‘Sheet1’!)

V3=formulas that give exact address of the value V1 on the sheet it is located in (‘[WORKBOOK.XLSM]SHEET1’!$X$1)

V4= formula that translates V3 into the column number (24)

V5=value that calls form2 to be called if combobox value on form1 matches it. This value is also what I need to find on Sheeet1 as I explain below.

On Sheet1 there is data in the range X1:AAU102

Within that range, I want to put the value of the combobox on Form2 into the cell within the above range that has the same value as V5 on the Master Info sheet. The formulas I mentioned earlier help me determine the sheet it is on, but there are 14 other identical sheets that it could be, so I cannot reference one sheet specifically, hence why I used the formula. The value of V5 on the Master Info sheet could also be located anywhere within the range (sheet1 X1:AAU102) which Is why I determined the column number via formula. So, I know what sheet and what column to search in to match the value of Master Info V5, but I do not know how to find the row within that column and how to replace the it with the value of Form2 combobox.

To clarify, when combobox on form2 changes, I want to put the value of the combobox into Sheet1 in column number listed in masterinfo v4, replacing the cell that has the same value and master iinfo v5 with the form2.combobox.value

When I initially planned on this, I didn’t think far enough into to figure out this is something I cannot accomplish on my own. Can anyone assist me with this task or point me to a resource that could help me to accomplish it? I thank you for your time and consideration in advance.

 

[Logit]

.
Your lengthy description is confusing for me. Sorry.

The title of your post indicates you want to replace a term where ever it may appear in a workbook. The following macro
will accomplish that task.

Sub FindReplaceAll_CountReplacements()
'PURPOSE: Find & Replace text/values throughout entire workbook, notify user of how many cells were affected
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
'Dim fnd As Variant
'Dim rplc As Variant
Dim ReplaceCount As Long

Dim fnd, rplc As String

fnd = InputBox("Enter OLD term. ", "Old Term ?")
rplc = InputBox("Enter NEW term. ", "New Term ?")

For Each sht In ActiveWorkbook.Worksheets

  ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")

  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
Next sht

MsgBox "I have completed my search and made replacements in " & ReplaceCount & " cell(s)."

End Sub

 

[robertmwaring2]

.
Your lengthy description is confusing for me. Sorry.

The title of your post indicates you want to replace a term where ever it may appear in a workbook. The following macro
will accomplish that task.

Sub FindReplaceAll_CountReplacements()
'PURPOSE: Find & Replace text/values throughout entire workbook, notify user of how many cells were affected
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
'Dim fnd As Variant
'Dim rplc As Variant
Dim ReplaceCount As Long

Dim fnd, rplc As String

fnd = InputBox("Enter OLD term. ", "Old Term ?")
rplc = InputBox("Enter NEW term. ", "New Term ?")

For Each sht In ActiveWorkbook.Worksheets

  ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")

  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
  
Next sht

MsgBox "I have completed my search and made replacements in " & ReplaceCount & " cell(s)."

End Sub

I appreciate your response, but this isnt at all what I was looking for. I'm sorry my description is confusing - I tried hard to explain it thoroughly to avoid that very situation.
I do not need to replace it EVERYWHERE in the workbook, simply in a specific column, (which can vary). The value I need to replace may well be listed in other columns throughout the workbook, possibly even on the same worksheet, but I only need to replace it in a particular column. Through formulas, I am able to determine what sheet and which column number the value to be replaced is, but from there I cannot figure out how to find specific cell address of the value I need to replace using the value of the combo box from Form2 once the user selects it as the replacement.