This is an extension of my post found here, but trying to approach the problem from a different perspective.
http://www.excelforum.com/showthread...t=#post4440273
Based on extensive internet searching and studying VBA (first venture into this world) I have found a code to find a value
But now I need to develop it in two big ways.
1) End result should be to replace the value with another value in a different cell, different column but same row (i.e.("f6").value replace with range("i6").value
2) As this record is repeating, it should loop through the search, redefining "i" and "findstring" and searching until "findstring"="" (i.e. search based on f6, after calculation is complete, search based on f16, f26, etc.)
The code, which I lightly modified, is as follows: (any advise for improving speed or reliability is appreciated. Total newb)
Private Sub ButtonFind()
'adaptation from scott's code
'http://stackoverflow.com/questions/12642164/check-if-value-exists-in-column-in-vba
Dim FindString As String 'defines value to be found
Dim Rng As Range
Dim Lan As String 'defines language selected
Lan = Sheet2.Range("e9").Value
Dim i As Integer 'defines step value for records. current value is recorded in f(6+10*i)
i = 6
FindString = Sheet3.Range("f" & i).Value 'stored value occurs once every ten rows in "f"
If Trim(FindString) <> "" Then
If Lan = Sheet1.Range("KOR").Value Then
With Sheet6.Range("ClashIssuesEng") 'searches all of ClashissuesEng range
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True 'value found (need to replace this formula with a replacement cell value based on this values row location)
Else
MsgBox "Nothing found" 'value not found
End If
End With
End If
If Lan = Sheet1.Range("ENG").Value Then
With Sheet6.Range("ClashIssues") 'searches all of Clashissues range
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True 'value found (need to replace this formula with a replacement cell value based on this values row location)
Else
MsgBox "Nothing found" 'value not found
End If
End With
End If
'i = i + 10 needs to loop until findstring=""
End If
End Sub
Bookmarks