PDA

View Full Version : Can't change Range.value



scotwww
10-15-2018, 07:25 AM
I have been going mad for the past few hours. Have a search code that aims to compare a range of cells to a particular search string.
I have validated that it searches at least 1 cell for substring matches but get stuck trying to print my answer to a cell. Simple as that. I know the column and row that I want to print to, I know the string that I want to put there but I keep getting 1004 and 424 errors. I have no problem reading a range.value, but assigning one is a different story. VBA is good when it works but I'm not a fan today. Any help would be wonderful. Code follows:


'Arguments: srch is string to be found, colRng is a column range, minMatch is min number of characters for match
'Advise to use srch in right most column in the spreadsheet. Adjacent cells to the right will be populated with
' possible matches to the srch string in the colRng range
Public Function findStr(srch As String, ColRng As Range, minMatch As Integer)
Dim writeRng As Range
MsgBox ActiveCell.Address
printrow = ActiveCell.Row
printcol = ActiveCell.Column + 1 'WARNING!!!! Will overwrite cells adjacent to search
printcolLetter = Split(Cells(1, ActiveCell.Column).Address, "$")(1)
For Each cell In ColRng
x = cell.Value
cellLen = Len(x)
cellRow = cell.Row





For srchLen = cellLen To minMatch Step -1 'check for complete matches and down to matches with minMatch Characters
startChar = 1 'reset start character in search

While startChar + srchLen - 1 <= cellLen
strsch = Mid(x, startChar, srchLen) ' current substring of current cell to compare
'MsgBox ("StartChar: " & startChar & " srchLen:" & srchLen & strsch & " StringCompare:" & StrComp(strsch, srch, vbBinaryCompare))
If StrComp(strsch, srch, vbBinaryCompare) = 0 Then 'returning 0 indicates possible match
Worksheets(1).Activate
Set writeRng = Range(Cells(printrow, printcol), Cells(printrow, printcol))
Call writeRange(writeRng, x)

'Cells(printrow, printcol).Select

printcol = printcol + 1 'increment for next possible result
startChar = cellLen + 1 'flag end of while loop as printed matched output for this cell

End If




startChar = startChar + 1

Wend
Next srchLen
Next cell
End Function


Public Sub writeRange(rng As Range, txt)
rng.Value = txt
End Sub

Paul_Hossler
10-15-2018, 07:47 AM
1. I added CODE tags to your post - you can use the [#] icon to add CODE ... /CODE tags and paste your macro between

2. At a qucik glance nothing jumps out, but there's no test data or example of how it's used. I'm guessing that there's another sub??

Use [Go Advanced] and the paper clip to attach an example to make it easier

scotwww
10-15-2018, 01:15 PM
Thank you, Paul. I'm runnning the function in cell C4. The subroutine is just a short one liner because I was getting desperate. For test data that can be debugged at the command line, run from C4 and add breakpoint at "Call writeRange(.." Thank you very much.

Paul_Hossler
10-15-2018, 02:58 PM
I'm not sure that you can get that approach to work since functions return values and can't do much to the worksheet

I made a simple sub with a calling driver

I couldn't figure out the reason for the min number of characters

InStr is case sensitive this way




Option Explicit

Sub drv()
Call findStrings(Range("C2").Value, Range("A4:A18"), Range("C4"))
End Sub

Public Sub findStrings(srch As String, ColRng As Range, OutLoc As Range)
Dim C As Range

For Each C In ColRng.Columns(1).Cells
If InStr(C.Value, srch) > 0 Then
OutLoc.Value = C.Value
Set OutLoc = OutLoc.Offset(0, 1)
End If
Next
End Sub

scotwww
10-16-2018, 08:03 AM
Thank you very much Paul. You helped me by stating that functions can't change worksheets. I didn't realise this for some reason. I think it comes back to the definition of a function as something that returns a value but doesn't change any other states. I wanted a substring similarity search for two columns with a minimum number of shared characters in a substring.

Paul_Hossler
10-16-2018, 08:18 AM
Functions CAN change worksheets since that's how they return a value. They just can't change a lot of things on a WS (things like Interior, Font, etc.) and there are some VBA properties that won't work in a function (e.g. .CurrentREgion)