Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. jlt199

    Using a user input string as part of the name of a range

    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.
  2. jlt199

    Using a user input string as part of the name of a range

    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)
  3. jlt199

    Using a user input string as part of the name of a range

    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
  4. jlt199

    Using a user input string as part of the name of a range

    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...
  5. jlt199

    Using a user input string as part of the name of a range

    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...
  6. jlt199

    Formula written in Macro not evaluating

    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.
  7. jlt199

    Formula written in Macro not evaluating

    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 & _...
  8. jlt199

    Formula written in Macro not evaluating

    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...
  9. jlt199

    Formula written in Macro not evaluating

    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...
  10. jlt199

    Formula written in Macro not evaluating

    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...

Part and Inventory Search

Back
Top