-2

I wrote a function, where you put a column name as a string and row number. Then it goes to that cell and changes inside formula, let's say sheet1 to sheet2

But when I apply it, many times excel shuts down. What can be reason. Here's code

Public Function formchange( c As String, n As Integer, Optional valueLookFor As Variant, Optional valueChangeTo As Variant) As Variant

valueLookFOr ='sheet1'
condOne = ThisWorkbook.ActiveSheet.Range(c & n).Find(What:= valueLookFor)
valueChangeTo = 'sheet2'
If condOne <> 0 Then
    ThisWorkbook.ActiveSheet.Range(c&n).Replace(valueLookFor,valueChangeTo).Select
End If
End Function
xlmaster
  • 659
  • 7
  • 23
  • 2
    How exactly are you "applying" it? – BigBen Dec 06 '21 at 16:48
  • 1
    in a excel spreadsheet. on formula bar...Is it what you asked for? =formchange("F";7;"sheet1";"sheet2") – xlmaster Dec 06 '21 at 16:51
  • 3
    you cannot `.Select` a cell that is using `.Replace` Also `Find` will return `Nothing` not `0` if the value is not found in that cell. Also, with Find you want to use more of the criterion to ensure you are looking at the formula and that you are looking at part of the string and not the full string. Also the Find is not needed as the replace will not error if the value to be replaced is not found. Also a UDF called from the sheet cannot make changes to cells other than return a value to the cell in which it called, unless you do some hacks. – Scott Craner Dec 06 '21 at 16:52
  • What is `.Select` supposed to do? `.Replace` returns a Boolean, so selecting that return value isn't possible. – John Coleman Dec 06 '21 at 16:52
  • I use everytime Select or Row afetr Find method as it gives error like I have to assign a variable – xlmaster Dec 06 '21 at 16:54
  • 1
    Tangential to your main question, byr you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248). – John Coleman Dec 06 '21 at 16:56
  • So? Not any assistance in fixing the code? Or offering other path to reach the maintained result? – xlmaster Dec 06 '21 at 17:02
  • 1
    I'd say you've had quite a lot of assistance. Have you read them and the links provided? – SJR Dec 06 '21 at 17:15
  • I am coming to a conclusion that, better I change function to subprocedure and only use Replace method. or I will try now, first, to take the formula inside cell, to a variable then to replace inside that variable. and giving back the replaced value... – xlmaster Dec 06 '21 at 17:17
  • There is not any kind of assistance. Assistance would be to give the reasons why not to assign any variable to Find method or Replace method. The given link I have read it, before. So... – xlmaster Dec 06 '21 at 17:19
  • 3
    The short answer: don't call this from a cell, make it a `Sub`, and fix all the issues outlined above. – BigBen Dec 06 '21 at 17:56

1 Answers1

2

As said in comments, a function cannot change cells other than the one it is on. So,I would rather write:

Sub frmCh_caller()
    '
    formchange "a", "6"
    '
End Sub

and the function

Sub formchange(c As String, n As Integer, Optional valueLookFor As String, Optional valueChangeTo As String = "")
Dim dumstr As String, condOne as String

If valueLookFor = "" Then valueLookFor = "sheet1"
If valueChangeTo = "" Then valueChangeTo = "sheet2"

With ThisWorkbook.ActiveSheet.Range(c & n)
    condOne = lcase(.Formula())
    If Len(condOne) > 1 Then
        dumstr = Replace(condOne, valueLookFor, valueChangeTo)
        .Formula() = dumstr
    End If
End With

End Sub

There it is. With the Caller-sub, things are much simpler :) cheers

Apostolos55
  • 574
  • 3
  • 8
  • Thanks! For my curiosity, is it function even though you called it as Sub(procedure)? – xlmaster Dec 09 '21 at 20:53
  • 1
    in VBA a function returns something, while a Sub just does 'things'. A function used/called from the sheet has lots of limits/restrictions. A sub that is called from code or buttons is much more 'powerful'. Cheers – Apostolos55 Dec 10 '21 at 22:46