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

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
104
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,012
.
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.

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

Board Regular
Joined
Mar 8, 2019
Messages
104
Office Version
  1. 365
Platform
  1. Windows
.
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.

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,974
Messages
5,639,332
Members
417,081
Latest member
Jeyaraj2021

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
Top