Search results for query: *

I actually noticed that this code would not pick up the case where the illegal character is the last character in the string. I have therefore moved varStr = Left(varStr, j - 1) into the 'Case Else' statement above 'Exit For' and removed that If-statement altogether.

Sorry, should be varStr = Replace(varStr, " ", "") For j = 1 To Len(varStr) Select Case Mid(varStr, j, 1) Case "a" To "z", "A" To "Z", "0" To "9" Case Else Exit For End Select Next j If j < Len(varStr) Then varStr = Left(varStr, j - 1)

varStr = Trim(varStr) For j = 1 To Len(varStr) Select Case Mid(varStr, j, 1) Case "a" To "z", "A" To "Z", "0" To "9" Case Else Exit For End Select Next j If j < Len(varStr) Then varStr = Left(varStr, j - 1) Thanks

I'm not worried about the first character being legal, I'm adding a prefix to the user entered string to form the name. I hadn't considered the cell reference issue though... Is it possible to code something such as if illegal name remove last character, then keep looping until name is legal...

Hi I'm building a macro where I want to use part of a string input by a user as the name of a named range. The user can put anything in the string - the strings are used as the column headers of the output of the macro. As part of the macro I need to define a number of named ranges. I would...

I had originally used .value I've only switched to .FormulaR1C1 in the last few minutes. I guess if you start with one convention then you have to stick to it and Excel doesn't like you mixing them up.

Ok, I've fixed it! It was a problem with the OFFSET function, but I still don't really understand why. I have now changed the way the formula is constructed in the macro from .Range("H" & 6 + i).FormulaR1C1 = "=if(row() = R15C3,R5C2,IF(OFFSET(" & iCodes(i, 1) & sheetNum & _...

It suggests the problem is with OFFSET(GFDBG!A:A,,K8-1) at least that is where the #NAME? appears. However, I have used similar code in other formulas and it works fine. Could the problem be with the nested if-statement? What I don't understand is that when I go to the formula bar for that...

Thanks, I've tried that. But it didn't work. All my other formulas are working. The formula is =IF(ROW() = $C$15,$B$5+$B$7,IF(INDIRECT(ADDRESS(E8+1,K8,,,D8)) < $C$18,E8,MATCH($C$18,OFFSET(GFDBG!A:A,,K8-1),1))) If the first if-statement is true then it calculates no problem. It's only when...

Hi, this is my first time on this forum. I've been muddling through with building macros in Excel, but have found a problem I can't solve. My macro opens a new workbook, reads in and sorts/filters data from a text file before analysing it. I've input a formula into cell using the macro, but...