none
VB XL: for hit enter on required cells in a row RRS feed

  • Question

  • update:  rewrote this for some clarity.  pardon line returns copy from backup..  advised to post at this link.  am i ok?

    note:  as per subject line,  need item to work on 1 line of sheet,  better if does selected lines (as examples below), for eg:  1 column cells if 3 rows selected, then entirerow / performed on all 3.  thanks.

    hi,  i am trying to find a way to fix some name errors in a row.

    for a UDF i am using, they arise from calculating a row and some kind of name resolution miss, to the udf.

    hitting enter on the cell corrects the problem.

    this post is a continuation from post:

      http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother/vb-find-the-last-row-in-a-slection-of-cells-in-a/1fcc0825-c8f4-43d6-ad00-23d8f9df9209

    details: 

    a quick hilite of 3 formulas pasted in a column/ for 3 lines..

    & then run the macro calculates each line quickly  (cutting time by 2 3 4... rather than try to calc each line separately).

    i already know i could have run a line of script i already have that would do the same for all lines / entire rows:

     - problem:

    Selection.EntireRow.Calculate     'name errors arise in row  (intermittently)

    each row calculated might go to a  #NAME Error, intermittently.  (some kind of resolution problem, if udf not written completely well?),  my fix / work around:

     - quick answer:  (imagine dozens of these to perform the task for 1 row, then run 3 times to catch all dependants).

        If IsError(Cells(ActiveCell.row, "R:R")) Then  'yes
          Cells(ActiveCell.row, "R:R").Formula = Cells(ActiveCell.row, "R:R").Formula   'yes
        End If

    all that is needed to clear the name error problem (just by hitting enter on cell).  problem:  maybe 10 other columns, each that can affect 3 or 10 other dependant cells that row..  running the above 3 times,  to catch all dependants by testing for error cells, so formula = formula is only done once.

    HELP NEEDED:

      would be something similar to selection.entirerow for calc,  to do the same for hit enter on all error cells.

    choice 1.  calc all selection.entire rows,  (so if for this problem, not sure of best method/ if exists..  to just do 1 row, or this, selection.entirerow)

    choice 2.  calc row   for:

    Selection.EntireRow.Calculate     'does both, for 1 row  & selected rows

    for

    WORD PROBLEM EG:  answer might be like

    selection.entirerow.all name error cells.formula = cells.formula

    for if iserrror(cells(activecell.row, "r:r")) then    'change "r:r" to ALL cells in row? 

    (concept problem:  i run item 3 times? after separate line calc's to see where at..  else a loop that does the same?)

      - lends to item have been looking for:  is there a 1 line test for:  if there an error anywhere in line then  (no matter what cell in row is selected)

    i have the example for run code on all lines selected

    thanks in advance.


    • Edited by Davexx Tuesday, September 27, 2016 2:01 PM
    • Moved by Steve Fan Wednesday, September 28, 2016 2:08 AM
    Tuesday, September 27, 2016 1:29 PM

All replies

  • Hi,

    This forum is for general questions and feedback related to Microsoft Office. Since your question is more related to VB, I'll move it to the Excel for Developers forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Steve Fan


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, September 28, 2016 2:07 AM
  • Hi,

    You can refer old post always, but as we were not with you from beginning it is bit hard to follow you in above post.

    First mention what your problem/target in as detail possible. Mention what are the excel enviornments like sheet name, file name, range name etc.

    Then mention what have done so far to solve that. It seems you have already halfway towards solution

    Then mention at what line you are not expecting desired output or code is breaking. And how it is deviating from desired output


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, September 28, 2016 3:16 AM
    Answerer
  • Hi,

    For the code you are using now from the link, I could not figure out what you want to do without your data and UDF. Please share more detail information with us.

    It seems that re-entering the formulas could fix your issue, I suggest you try to use Application.CalculateFullRebuild Method (Excel). The CalculateFullRebuild method is similar to re-entering all formulas.

    Thursday, September 29, 2016 7:14 AM
    Moderator
  • NOTE:  wrote alot,  can just jump to items below:  "3 Questions"  & see the vb.  thanks.

    hi,  thanks for all the replies.  i will make a note of link in 2nd? post to try to post in correct area in future.

    some of this may not be heavily related, but may be a few things going on,  so just to say..

     - novice vb, i don't get some of the simple stuff, but with extreme hours spent on testing vb, i have pulled some tricks out. example of small? problem  (huge for me),  thought my sheet was corrupt past large file size (1 or 2 other things.. eg each new comment insert # at 447,609 ..).  Calc Problem:  posted for quote "years", not getting help.  problem/ answer:  as soon as i use:  EVENTS TRUE, my sheet works:  for calc & save.

     - i have had success at making redundacies to help keep sheet working.  for this problem a UDF i am using (roughly:  istrue() for if cell isnumber & > 0  hi repetition..  still causes some kind of name resolution between my sheet, and the vb. (if i am correct).  hitting enter on the cell clears the error.

    i have an answer but the scope of variations has been a chore.  (vb repair attempts making name errors to other items:  hyperlink formulas, a different vb udf using:  QLINK  scroll row method replace most hyperlinks, "no more offsets/ counting rows.."

    original method to clear: was a manual?  .formula = .formula,  for each cell in a row i thought would go error, eg dozens of:

        If 1 And IsError(Cells(ActiveCell.row, "R:R")) Then  'yes
          Cells(ActiveCell.row, "R:R").Formula = Cells(ActiveCell.row, "R:R").Formula   'yes
        End If

    BIG NOTE:  (sorry caps)  I AM LOOKING FOR ANSWERS THAT JUST DO ONE LINE.  that should make the task much simpler.  i have vb that will cycle thru a sheet,  that info from 1st post other link,  may just confuse things..  just 1 / whole line answer needed.

    i had some help from someone with the following.  it is heavily modified but includes rough 2nd part from their work.  gotta say i tried to heck to get help to fix it & it works now  (AFTER MAY HOURS :)  of work on it this weekend.  aside form maybe 5 long days before,  maybe 16 hours+ again.

    ANSWER below, has been tested for 1 line.  my running it on multple lines will be no problem.  if you want to help with these questions.

     - have not tested in header yet for that scene  (many hyperlinks wiped out, qlink udf's name errored, array formula's wiped out..)  don't have to address that, not enough info now thanks.

     - if speed is a concern (when i run it down a sheet),  which vb is faster?  of the 2, guesse the second might be:  IF IT JUST GOES FOR THE ERROR CELLS.  that is part of the problem trying to work out.  i don't know what the vb is doing for what is written.  version 1 & 2 seem just as fast at 1 line.

     - the couple of mistakes that were overlooked / could not get help on were,  PUT:    .CELLS at the end of the line:  Set rngErrors =

     - other item:  (if correct)  cells not addressed in:    rngErrors.Formula = rngErrors.Formula    so i just used my original:  c.Formula = c.Formula

    .

    ==========    MAIN:

    3 QUESTIONS WOULD ASK:  in the following vb, is the 2nd example faster, by making address only the error cells? (2 items separated by: IF 1 THEN, ELSE):

    For Each c In rngErrors

    AND 2:  is there a loop method to make it more efficient.  the test for no errors in line, i think makes it efficient but how to autmate it besides my running it 3 times,  eg: 

    call fixROW

    call fixROW

    call fixROW

    AND 3:  vb has halted on cells > 256 characters.  need to skip those (text?) cells.  i am using windows xp right now (there was a calc sheet problem worked on).

    are there any other recommendations to the code.  i was forced to have to do it myself & spent too much time on it, but here it is.

    Sub fixROW2()    'clear name errors with .formula = .formula
        Dim rngErrors As RANGE, cellError As RANGE
        Dim s As RANGE, c As RANGE
        Set s = Selection.EntireRow.Cells     'RIGGIN YES?  (ANS:  put .cells at end)
        'application.ScreenUpdating = False            'UPDATE   screen jump
        RANGE("A1").Value = vbNullString    'simple safety by:  If RANGE("A1").Value > 0 Then
        
        'With ActiveSheet
        'With Worksheets("sym")      'Edit "Sheet1" to your worksheet name
            On Error Resume Next    'If no error cells then next code line errors
            Set rngErrors = ActiveSheet.Rows(Selection.row).SpecialCells(xlCellTypeFormulas, xlErrors).Cells   'works for rows
            'Set rngErrors = .Rows(Selection.row).SpecialCells(xlCellTypeFormulas, xlErrors).Cells   'YES (needed .cells) works for rows, need other for cells?
            On Error GoTo 0         'Resume error trapping ASAP.
        'End With
        
    If 1 Then    'my example,  now working
        If Not rngErrors Is Nothing Then              'yes works here
        For Each c In s    'gd
          If IsError(c) And c.HasArray = False Then   'And Len(c.Formula) <= 256 Then     'LEN not seem to work here
            c.Formula = c.Formula    'yes
            'rngErrors.Formula = rngErrors.Formula       'NO, this item pastes 1st err cell formula to other error cells, so is wrong to start with
          End If
        Next
        Else
          'MsgBox "yep1" & Space(15), vbQuestion
        End If
        

    Else    'other ver:  also working,  item faster than above?:  For Each c In rngErrors
        If Not rngErrors Is Nothing Then
        For Each c In rngErrors       'WIPING OUT 2 ITEMS?:  ARRAY FORMULAS & HYPERLINKS (intermittent?)
          If c.HasArray = False Then  'yes but if have an array in line, run this loop 3x instead of 2x.  'If rngErrors.HasArray = False Then  'no  'And Len(rngErrors.Formula) <= 256 Then   'cannot get LEN to work
            'MsgBox c.address & SPACE(15), vbquestion   'test
    'PROB:  here? need to isolate error formula for that cell (above?), not 1st cell detected to all other error cells.
            
            c.Formula = c.Formula    'yes
            'rngErrors.Formula = rngErrors.Formula   '< WRONG  this item pastes 1st err cell formula to other error cells, so is wrong to start with
          End If
        Next c
        
        Else
            'MsgBox "yep2" & Space(15), vbQuestion
        End If
    End If
        
        'goBeeps (2), (0.25)       'beeps secs
        'MsgBox "Done" & Space(15), vbQuestion
        application.EnableEvents = True               'EVENTS
    End Sub
    'fixrow2() FIXROW2()

    'thanks in advance.




    • Edited by Davexx Sunday, October 2, 2016 8:02 PM
    Sunday, October 2, 2016 7:38 PM
  • if might not think to need?  am using 2 major udf's:  isTRUE()  & QLINK()

    yep,  wrote alot, sorry :)  this is the whole story & i'm stickin to it.

    not sure if there is a better way to write this to fix name errors.  tried to look it up once,  where saw comment on having part of code outside the udf makes it more stable,  as i have seen here.  the eg i have for qlink is thus, & more stable.

    Function isTRUE(ByRef x As RANGE) As Boolean      'call as:  IF(IS TRUE(A1),1,0), not ready for a range of cells eg:  A1:B1
        On Error Resume Next                          'for combine:  AND(if(isnumber(a14),a14>0)
        'Application.Volatile (True) 'forced recalc overkill?
        'x = x.ToUpper()    'no
        'byref = ucase(byref)   'no
        'UCase(isTRUE) = UCase(isTRUE)   'no, to caps not work
        'if UCase(Left(strFormula, 6)) = UCase("=QLINK") then   'eg in sheet code
        
        isTRUE = False        'used to simulate test cell:  if isnumber & if >0,  need help making enter range of cells possible
        'If X.Value > 0 Then isTrue = True   'during download:  err 13: type mismatch for:  If X.Value > 0 The  (lookup)
        If IsNumeric(x.Value) And x.Value <> vbNullString And x.Value > 0 Then isTRUE = True   'new, seems to work
        'If IsNumeric(x.Value) And x.Value <> vbNullString Then isTRUE = True   'new  yes
        If Not IsNumeric(x.Value) Then isTRUE = False
        
    'RESET FUNCTION CASE:    (other? resolution problem)
      'rename function name eg: sameNAMEx to desired case & "x", COMPILE VB (ignore error;  debug: compile, rem x from name, COMPILE VB, then:
      'sheet: rename function in a cell to desired case & hit enter, (done),  test: paste same formula in a different cell in lower case (case should correct self)
      'if case in sheet problem reoccurs, what vb to ck / force fix case ?  udf now not compiled?
    End Function    'istrue() ISTRUE()

    .

    if not too much,  the other item QLINK is a great substitute for HYPERLINK:

    .

    Function QLINK(rngToSel As RANGE, strFriendlyText As String) 'add to a module,  see sheet1 qlink
        QLINK = strFriendlyText
    End Function    'for:  =QLINK($A$200,"A")  or  =QLINK($AD$200:$AF$200,"Friendly Name")

    '

    '

    'With Target
    '    If .Count > 1 Then 'Exit Sub
    '      Application.EnableEvents = True                      'EVENTS
    '      Exit Sub
    '    End If
        
    'If RANGE("A1").Value > 0 Then   'easy safety macro's on/off,  do not use with doubleclick version
        
        Dim strFormula As String, strRefToSel As String         'QLINK  quick link:  single click scroll row to destination for top of view, moving up -or- dn.
        Dim rngToSel As RANGE                                   '
        On Error Resume Next
        strFormula = Target.Formula
        
        If UCase(Left(strFormula, 6)) = UCase("=CLINK") Then    'for?
        End If
        
        If UCase(Left(strFormula, 6)) = UCase("=QLINK") Then    'call as:  =QLink($A$897,"friendly name")
            strRefToSel = Mid(strFormula, 8)
            strRefToSel = Left(strRefToSel, InStr(strRefToSel, ",") - 1)
            Set rngToSel = RANGE(strRefToSel)         'remove:  Me.  to keep from selecting destination up front & get rid of update off.
            'Set rngToSel = Me.RANGE(strRefToSel)     'orig
            
            If countChar(UCase(Selection.Formula), UCase("offset")) > 0 Then   'YES (thee? single click answer)
              application.DoubleClick
            End If
            
            If Not rngToSel Is Nothing Then           'jump to rng & do scrolling
              If rngToSel(ActiveCell.row, K3) = "IN" Then       'Or Selection.OFFSET(-1, 0).Value = "." Then        'offset interfere with TOP
                ActiveWindow.ScrollRow = rngToSel.row - 0       '<< SCROLL  yes   '(row, col)
              Else
                ActiveWindow.ScrollRow = rngToSel.row - 1       '<< SCROLL  yes   'rngToSel.Select                 'orig
              End If
            End If
        End If


    • Edited by Davexx Sunday, October 2, 2016 7:58 PM
    Sunday, October 2, 2016 7:57 PM
  • Hi,

    You could use code below to test how long it costs.

    Sub speed()

    Dim StartTime As Double

    Dim SecondsElapsed As Double

    StartTime = Timer

    'test code here

    SecondsElapsed = Round(Timer - StartTime, 2)

    MsgBox "Time: " & SecondsElapsed & " seconds", vbInformation

    End Sub

    Friday, October 7, 2016 3:57 PM
    Moderator
  • hi Celeste..  thanks.  i will check that out.  not sure what to categorize myself as,  :)  been doing novice vb for awhile & not sure if will know the difference from an eg i already had.  but i will check out best i can.  the example i had gotten maybe a year ago looks less than yours, and only variation can think of would be how to call it otherwise when needed / for tasks i already run  (aside from running a separate 1 time test),  had:

    Sub Timer_Test()    'if destructive, do a save / backup first
        Dim startTime, endTime
        startTime = Timer   'RUN YOUR CODE HERE:
          Call altT1        'run code
        endTime = Timer
        MsgBox "Timer: " & endTime - startTime & Space(15), vbQuestion, "Timing:"
    End Sub

    Wednesday, October 19, 2016 12:08 AM
  • note:  still pretty novice at vb.

    hi,  thanks for replies.  gone awhile,  running slow - get bogged down first of month.  i still need to review suggestions made here, will get back.  (pardon writing alot for way posts have gone before:  told not enough info/ hangup on post had forced add details :) 

    not sure if can get help on review what i have done.  i was finally able to make it work.  runs better than what had originally for fixing name errors on 1 line in sheet (from udf's that i use:  some name convention - disconnect to vb? error),  but seems like could run faster - more efficient.

    what the following code does is to basically hit enter on #Name error cells,  to clear the error.  i use it often for each row in sheet that gets updated / calc & cut row.  1 out of 5 or 10 rows gets multiple name errors, to include dependant cells in same row.

    i had 2 problems in code that i resolved,  eg:  .cells  added to 1 of the dim items, and replacing 1 line of code as listed.

    i have to run this item 3 times to get all dependant cells to be cleared of name errors, each time i go to calc/cut a row.

    ==========    WORKING EXAMPLE:   Question / Problem:  can it be improved to run more efficent / faster, in 1 row?  thanks

    problem example:  it halts on text cells > 256 chars.  (just need to skip those cells, not sure how).  other:  anything to make it faster?  i am not able to make new:  With / End Withs,  if that applies.  HYPERLINKS get wiped out (if running header version of this).

    i have code to run it down a sheet, 1 line at a time.  that is mostly all that is outside of this post but does not affect this request.  i can post if desired.

    Sub fixROW2out()    'clear name errors in a row with .formula = .formula
        Dim rngErrors As RANGE, cellError As RANGE
        Dim s As RANGE, c As RANGE
        Set s = Selection.EntireRow.Cells     'YES  (ANS, put:  .cells at end)
        RANGE("A1").Value = vbNullString      'simple safety macros off by:  If RANGE("A1").Value > 0 Then
        
        'With ActiveSheet    '<<  was not sure how to make work
            On Error Resume Next    'If no error cells then next code line errors
            Set rngErrors = ActiveSheet.Rows(Selection.row).SpecialCells(xlCellTypeFormulas, xlErrors).Cells   'works for rows
            On Error GoTo 0         'Resume error trapping ASAP.
        'End With
        
    If 1 Then   'EG1:  is there a faster / more efficient method?
        If Not rngErrors Is Nothing Then              'yes
        For Each c In s    'gd
          If IsError(c) And c.HasArray = False Then   'And Len(c.Formula) <= 256 Then     'LEN not seem to work here
            c.Formula = c.Formula    'yes
            'rngErrors.Formula = rngErrors.Formula    'NO, this pastes 1st err cell formula to other error cells, so is wrong to start with
          End If
        Next      '<<  CORRECT?  not sure if matters, why i don't have here:  Next c
        Else
          'MsgBox "yep1" & Space(15), vbQuestion
        End If
        
        
    Else    'EG2,  orig ver approx:
        If Not rngErrors Is Nothing Then
        For Each c In rngErrors
          If c.HasArray = False Then
            c.Formula = c.Formula    'yes
            'rngErrors.Formula = rngErrors.Formula   'NO  this item pastes 1st err cell formula to other error cells
          End If
        Next c
        
        Else
          'MsgBox "yep2" & Space(15), vbQuestion
        End If
    End If
        
        'goBeeps (2), (0.25)       'beeps secs
        application.EnableEvents = True               'EVENTS
    End Sub








    • Edited by Davexx Wednesday, October 19, 2016 2:00 AM
    Wednesday, October 19, 2016 1:33 AM
  • to include all of what doing,  and maybe of use to others.  i use this code to run various tasks that do:  1 line at a time.  i did not write it but did conceive.

    this is the example i used to run the code for / in this post, again as:  1 line aat for repair of name errors if they arise in whole sheet. 

    THIS IS THE ITEM that was posted previously/ outside this post,  but don't think has bearing on the single line vb working on.

    it should not affect the purpose of the question for this post, unless there is a better way..  (this method isolates affecting my hyperlink work lines,  which the code for:  fixROW2()  will wipe out hyperlinks.

    .

    FOR LOOPING THRU A SHEET,  1 LINE AT A TIME:  CAN USE FOR ANY OTHER TASK YOU WANT.

    '

    '

    Sub fixROWS()    'fix "rows"  plural
        Dim E7 As String    'note:  workcell E7 shows:  BG381
        E7 = RANGE("E7")    'see eg below
        Dim r As Long       'NOTE: this example has a paste formula(s) down column(s).  WILL REDUCE 10 HOUR DAYS OF PASTING COLUMNS, DOWN TO 3 MINUTES?
        Dim c As Long
        Dim rCell As RANGE  'each row cell?
        Dim LastRow As Long
        
        r = ActiveCell.row
        c = ActiveCell.Column
        LastRow = Cells(Rows.Count, 1).End(xlUp).row  'find last row
        
        application.EnableEvents = False              'EVENTS  must have?
        application.ScreenUpdating = False            'UPDATE  screen jump

    'vb runs from selected row, but you can have vb start from anywhere
        RANGE(E7).Select  'TOP ROW SELECT
        Call goCYC        'cycle down sheet to first record, skip spacer lines
        RANGE("A1") = vbNullString
        
        
        For Each rCell In RANGE(Cells(r, c), Cells(LastRow, c)) 'new
            rCell.Select
            
            If 1 And Left(Cells(rCell.row, "A:A").Text, 1) <> "." Then   'And Left(rCell.Formula, 11) = "=HYPERLINK(" Then   'not working,  target.formula?
              If 0 Then
                Selection.EntireRow.Calculate
                Call fixTRUE     'OLD  manual workaround fix for .formula = .formula picking specific columns in a row (many repetitions.. not pretty)
              Else
              
    'fixrows() FIXROWS()  vbrowseg() VBROWSEG()
              'Call fixROW1    'not working, wo: include hyperlink skip, fix pasting 1 error cell onto another, exclude array formula's
              Call fixROW2
              Call fixROW2
              Call fixROW2
              End If
            End If
    'other uses for process 1 row aat/ rund down a sheet,  make 3 macros for each paste macro below
              'Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False    'make sub alte ()    add thisworkbook:  application.OnKey "%{e}", "alte"
              'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False     'make sub altf ()    add thisworkbook:  application.OnKey "%{f}", "altf"
              'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False         'amke sub altp ()    add thisworkbook:  application.OnKey "%{p}", "altp"
            
            'Selection.EntireRow.Calculate
        Next rCell
        
        'application.CutCopyMode = False
        RANGE("A2").Select
        goBEEPS (2), (0.25)       'beeps secs
        application.EnableEvents = True               'EVENTS

    End Sub
    'note:  workcell E7 has:
    '=SUBSTITUTE(SUBSTITUTE(CELL("address",$BG$381),"$",""),"","")
        'If 1 And IsError(Cells(ActiveCell.row, "R:R")) Then  'yes
        '  Cells(ActiveCell.row, "R:R").Formula = Cells(ActiveCell.row, "R:R").Formula   'yes
        'End If


    • Edited by Davexx Wednesday, October 19, 2016 2:14 AM
    Wednesday, October 19, 2016 2:08 AM