Macro Code Should Change When entered Cell Value matched????

[amiable]

Hi,

Could you please me this logic,

In particular Cell, the value is entered, That value should be replace in VB macro code in particular line.

Example:

In sheet = C5 cell have "Submit"

in VB Macro code, In 5th line, "Start" code available, so now i need to replace the START to SUBMIT in vb macro.

[Norie]

Why do you need to change the code?

[Winon]

Hello amiable,

It would be a great help if we could see your actual Code where you want to make the change.

[amiable]

Am using different file name, so when am running the file, i need to change the file name from macro, so instead ed of go to edit->vb macro, If possible,in work only, if i enter the data, after that data will replace the code in particular line. If third person whats to run the code, just give the file name and use the short cut key to run the code.
So no need to go and modify the code.

[amiable]

Worksheets("602.vb").Activate
While end_process = False
On Error GoTo nxt
Cells.Find(What:=Sheet13.Cells(a, 1), After:=Sheet7.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
, SearchFormat:=False).Activate

This is sample code.
So i need to change the 602.VB name every time when am running thedifferent vb code and SHEET 7 also i need to change, SHEET7 is replacing the all value.

[Norie]

You don't need to change the code for that.

Read the value from the cell and then use that to identify the right worksheet.

Please Login or Register  to view this content.

PS When posting use code tags - it's kind of a forum rule.

[amiable]

Hi Norie,

I am facing Error 91, Subscript out of range, This is my code, I have updated the your code also.
In G5 Cell, I am giving the file name. In ws variable cell value is not assign. pls find the attachment

Please help this issue.

Sub Windows()

Dim ws As Worksheet
Dim rngFnd As Worksheet
Dim strSheet As String

end_process = False
a = 1

strSheet = Sheet1.Cells.Range("G5")

Set ws = Worksheets("strSheet").Activate


While end_process = False
     On Error GoTo nxt
    Cells.Find(What:=Sheet1.Cells(a, 1), After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
     xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
    , SearchFormat:=False).Activate
    
    sactrow = ActiveCell.Row
    sactcol = ActiveCell.Column
    cellcont = ActiveCell
    srchlen = Len(Sheet1.Cells(a, 2))
    end_require = False
    
    While end_require = False
        For x = 1 To Len(cellcont)
            If Mid(cellcont, x, srchlen) = Sheet1.Cells(a, 2) Then 
                pos = x
                newstring1 = Mid(cellcont, 1, pos - 1)
                newstring2 = Sheet1.Cells(a, 3)
                newstring3 = Mid(cellcont, pos + srchlen, Len(cellcont) - (pos + (srchlen - 1)))
                ws.Cells(ActiveCell.Row, ActiveCell.Column) = newstring1 + newstring2 + newstring3
                End If
        Next x
        Cells.FindNext(After:=ActiveCell).Activate
        cellcont = ActiveCell
        If sactrow = ActiveCell.Row And sactcol = ActiveCell.Column Then
            end_require = True
        End If
        
    Wend  

[amiable]

Please help me????