Page 3 of 61 FirstFirst 123451353 ... LastLast
Results 21 to 30 of 604

Thread: Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

  1. #21
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    did not work see here: where it did....
    http://www.excelfox.com/forum/f17/ht...eet-test-1899/

  2. #22
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    I apply this code

    Code:
    Sub Evaluate_Left()Dim rngName As Range
    Set rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
    Dim rngEE As Range
    Set rngEE = ThisWorkbook.Worksheets("sheet1").Range("E3:E10")
    Let rngEE = Evaluate("if(row(3:10),LEFT(" & rngName.Address & ",4))")
    End Sub 'Evaluate_Left()
    and again get wot I expact....

    see again...
    http://www.excelfox.com/forum/f17/ht...eet-test-1899/

    Now I apply this code

    Code:
    Sub Evaluate_VLOOKUP()Dim rngName As Range
    Set rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
    Dim rngCC As Range
    Set rngCC = ThisWorkbook.Worksheets("sheet1").Range("C3:C10")
    Let rngCC = Evaluate("if(row(3:10),VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE))")
    End Sub 'Evaluate_VLOOKUP()

  3. #23
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    and get this:

    see again:
    http://www.excelfox.com/forum/f17/ht...eet-test-1899/


    . I would have expected that result without The extra If Row() stuff, which I thought overcame this problem.
    . can anyone suggest wot is going wrong, or how I obtain the correct results (Using the Evaluate Function for a range)
    Thanks
    Alan.


    P.s. I can post a spreadsheet in the test area but not apparently here in a thread??. Can you help me on that one as well?

  4. #24
    Junior Member
    Join Date
    Jun 2012
    Posts
    8
    Rep Power
    0
    Hi DocAElstein, just as a pointer to your trouble with posting Html tables.
    On vBulletin based forums there is a box in the bottom right corner that tells you what posting permissions can be used on each forum.
    In this case HTML code is turned off.

    Using Office 2010
    Quite often have to save as '97-
    2003 for other users

  5. #25
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    Quote Originally Posted by MARK858 View Post
    Hi DocAElstein, just as a pointer to your trouble with posting Html tables.
    On vBulletin based forums there is a box in the bottom right corner that tells you what posting permissions can be used on each forum.
    In this case HTML code is turned off.
    Thanks.
    . Any idea how I can "turn it on"?
    Alan

  6. #26
    Junior Member
    Join Date
    Jun 2012
    Posts
    8
    Rep Power
    0
    You can't, the Forum Admin decide and control what permissions are allowed in each forum.
    In general in my experience it is quite general that if a forum allows attachments (like this one does) then HTML is switched off.
    Using Office 2010
    Quite often have to save as '97-
    2003 for other users

  7. #27
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    Quote Originally Posted by MARK858 View Post
    You can't, the Forum Admin decide and control what permissions are allowed in each forum.
    In general in my experience it is quite general that if a forum allows attachments (like this one does) then HTML is switched off.
    OK.,
    . I will look into the use of "attachments" next time!

    Thanks
    Alan

  8. #28
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10

    Appendix Thread. Evaluate Range ( Codes for other Threads, HTML Tables, etc.)

    Re: Appendix Thread. ( Codes for other Threads, HTML Tables, etc. )

    Hi
    . I would like to use this Thread as an Appendix for codes in other Threads so as to help reduce clutter in that Thread should the code be a bit long, or not directly relevant.
    . Also as HTML code is on in this Test Sub Forum I would like to reference HTML Tables should I wish to use them in answering threads

    @ Moderators, Administrator:
    . I hope the above is OK to do and if so please do not delete this Thread. ( Or advise if I should post my "Appendix" somewhere else ( If possible where HTML code is on ) )
    .
    . Many Thanks
    Alan



    This Post 2834 https://excelfox.com/forum/showthrea...ll=1#post18462
    https://excelfox.com/forum/showthrea...tc-)#post18462



    (Copied from 2345)
    Last edited by DocAElstein; 11-08-2022 at 02:35 PM.

  9. #29
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    Function Code for getting Column Letter from Column Number
    Shortened version used in Post #14
    http://www.excelfox.com/forum/showth...=9837#post9837
    Public Function CL(ByVal lclm As Long) As String

    And Fuller version with explaining ‘Comments


    Code:
    Public Function CL(ByVal lclm As Long) As String '         http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
        Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
    End Function
    
    Function FukOutChrWithDoWhile(ByVal lclm As Long) As String 'Using chr function and Do while loop      For example http://www.excelforum.com/excel-programming-vba-macros/796472-how-to-go-from-column-number-to-column-letter.html
    Dim rest As Long 'Variable for what is "left over" after subtracting as many full 26's as possible
        Do
        '    Let rest = ((lclm - 1) Mod 26) 'Gives 0 to 25 for Column Number "Left over" 1 to 26. Better than ( lclm Mod 26 ) which gives 1 to 25 for clm 1 to 25 then 0 for 26
        '    Let FukOutChrWithDoWhile = Chr(65 + rest) & FukOutChrWithDoWhile 'Convert rest to Chr Number, initially with full number so the "units" (0-25), then number of 26's left over (if the number was so big to give any amount of 26's in it, then number of 26's in the 26's left over (if the number was so big to give any amount of 26 x 26's in it, Enit ?
        '    'OR
        Let FukOutChrWithDoWhile = Chr(65 + (((lclm - 1) Mod 26))) & FukOutChrWithDoWhile
        Let lclm = (lclm - (1)) \ 26 'This gives the number of 26's ( if any ), but just the excact part, in the next number down , - so applying the rest formula to this new number will again leave a difference "left over" rest.
        'lclm = (lclm - (rest + 1)) \ 26 ' As the number is effectively truncated here, any number from 1 to (rest +1)  will do in the formula
        Loop While lclm > 0 'Only loop further if number was big enough to still have 0-25's in it
    End Function
    Rem Ref    http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Rem Ref    http://www.excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  10. #30
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10

    Grid coordinates for a Range using [ ] & Evaluate(" ") through a Named Range. Code 2

    Second Code with further lines to overcome extra () required for start row and star column codes


    Obtaining grid coordinates for an Area of contiguous cells in a Spreadsheet using [ ] and Evaluate(" ") through the use of a Named Range for that Area

    Aka ' It is a Range Name Test 2: Its n Range Name Test 2: 's 'n Rng Name Test 2: s n Rg Name Testie 2: snRg.Name = "snRgNme"
    This code is in support of other Posts in various Threads. ( I will edit the Links as I reference this post )




    The code takes in a hard coded Range, A1:E10.
    That Range is given a Name as held in the Names Register of a Workbook ( Workbooks Scope ).
    Various code lines are developed which reference this Named Range and return the Grid Coordinates.

    These coordinates are held within the following Long Type Variables
    Cs is the column count
    sClm is the start column
    stpClm is the stop column
    Rs is the rows count start row
    sRw is the start row
    stpRw is the stop row

    Code:
    ' Code 2
    '10   ' It is a Range Name Test 2: Its n Range Name Test 2: 's 'n Rng Name Test 2: s n Rg Name Testie 2: snRg.Name = "snRgNme"
    Sub snRgNameTest2()  ' Inspired by..   snb     .. " array [     ] "       '  http://www.excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9714#post9714
    20    ' Worksheets Info
    30    Dim ws As Worksheet '                                      ' Preparing a "Pointer" to an Initial "Blue Print" ( or a Form, or a Questionnaire not yet filled in, a template   etc.) in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Object of this type ) . This also us to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense )
    40    'Set ws = ThisWorkbook.Worksheets("NPueyoGyanArraySlicing") 'The worksheets collection object is used to Set ws to the Sheet we are playing with, so that we carefull allways referrence this so as not to go astray through Excel Guessing inplicitly not the one we want...              ' Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191
    50    Set ws = ActiveSheet ' Alternative to last line, make code apply to the current active sheet, - That being "looked at" when running this code        '
    60    Dim vTemp As Variant ' To help development when you are not sure what type is retuned. "Suck and see what comnes out!"  Highlight it and Hit Shift+F9 to see it in the imediate Window
    70    ' Named Range referrencing ' Workbooks ( Default ) Scope                                                                                                                                      Invoke  Pike  Evaluate Rabbit Rabbit. How's the Bunny ? Bunnytations Banters
    80    Dim snRg As Range: Set snRg = ws.Range("A1:E10")
    90    Dim sName As String: Let sName = "snRgNme" '
    100   Let snRg.Name = "snRgNme"  ' It is a Range Name me  - " 's 'n Range Name me "  ..  "snRgNme"  ;)  This name appears permanentlly in then sheet. It remains referrencing this range unless the name iis deleted or the range referrenced is overwritten by a similar code line which has a different range in it on RHS of =                                                                                                  http://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables
    110   Let snRg.Name = sName      ' Identical to last line
    120
    130    '== DANGER: === Pitful: Above we gave the Range Object a Name, but now see what  "Name"  or  "Name" 's  comes back "!"  !
    131  Dim clms() As Variant 'Array to take returned Variant type Field of sequential column numbers
    132     Dim retRefstrName As String, retObjName As Object
    133     Let retRefstrName = snRg.Name: Set retObjName = snRg.Name: Debug.Print snRg.Name 'something of the form   "NPueyoGyanArraySlicing!$A$1:$E$10" is reveald in Immediate ( Ctrl+G when in VB Editor ) Window
    134     'Let clms() = Evaluate("column(=NPueyoGyanArraySlicing!$A$1:$E$10)") 'Let clms() = Evaluate("column(" & retRefstrName & ")")' Rintime Error 13: Incompatiblee types
    135     Let clms() = Evaluate("column(NPueyoGyanArraySlicing!$A$1:$E$10)") 'Works
    137     Dim NameOnly As String: Let NameOnly = Replace((snRg.Name), "!", "", (InStr(1, (snRg.Name), "!"))):  'Debug.Print snRg.Name: Dim pos&: pos = InStr(1, (snRg.Name), "!"): NameOnly = Replace((snRg.Name), "!", "", pos) ' We had  ----  "NPueyoGyanArraySlicing!$A$1:$E$10" This is a String referrece returned when the Name Object is used directly or set to a String Variable.     so here I return a string that starts at the position of the ! and which replaces in that truncated shortened string -  "!$A$1:$E$10"   the "!" with nothing
    138     Let clms() = Evaluate("column(" & NameOnly & ")"): Let clms() = Evaluate("column(" & Replace((snRg.Name), "!", "", (InStr(1, (snRg.Name), "!"))) & ")")
    139
    140     Dim strName As String: Let strName = snRg.Name.Name: Debug.Print strName: Let strName = retObjName.Name: Debug.Print strName ' returns our original "CoN"
    142     Let clms() = Evaluate("column(" & strName & ")")
    150     Dim rngF1G2 As Range: Set rngF1G2 = Range("F1:G2"): Let Range("F1:G2").Value = "From Line 150"
    151     Let Range("=NPueyoGyanArraySlicing!F1:G2").Value = "From Line 151"
    152     Let rngF1G2.Name = "snFG": Let Range("snFG").Value = "From Line 152"
    153
    154
    159    '===============
    160   Let clms() = Evaluate("column(snRgNme)"): Let clms() = [column(snRgNme)] ' Full and "shorthand" Simple 1 D "pseudo horizontal" Array of column Indicies.
    170  '
    180   ' Count, Start, and Stop of columns in an Area of contiguous cells in a Spreadsheet
    190   Dim Cs As Long 'Variable for ColumnsCount.             -This makes a Pigeon Hole sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular "Value", or ("Values" for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. Long is very simple to handle, final memory "size" type is known (13.456, 00.001 have same "size" computer memory ),so an Address suggestion can be given for when the variable is filled in. (Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647). If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.-upon/after 32-bit, Integers (Short) need converted internally anyway, so a Long is actually faster)
    200   Let Cs = Evaluate("columns(snRgNme)") ' = 5
    210   'Let Cs = Evaluate("columns(RetunedsnRgName)") 'Run time Error as expected
    220   Let Cs = [columns(snRgNme)]           ' = 5              'Is this Most Powerful Command in VBA?, or what ...    http://www.ozgrid.com/forum/showthread.php?t=52372       http://www.mrexcel.com/forum/excel-questions/899117-visual-basic-applications-range-a1-a5-vs-%5Ba1-a5%5D-benefits-dangers.html
    230   'Let Cs = [columns(RetunedsnRgName)]           'Run time Error as expected
    240   Let Cs = [columns(A1:E10)]             ' = 5
    250                                                               Let vTemp = Evaluate("column(snRgNme)") ' Reveals an Array {1, 2, 3, 4, 5}  -  1 Dimension "pseudo Horizontal" Array
    260   Dim sClm As Long 'Variable for Start Column
    270   Let sClm = Evaluate("column(A1:E10)")(1)
    280   Let sClm = Evaluate("column(snRgNme)")(1) ' = 1
    290   Let sClm = [column(A1:E10)]()(1)
    300   Let sClm = [column(snRgNme)]()(1)
    301
    302   Let sClm = Evaluate("=MIN(column(snRgNme))"): Let sClm = [=MIN(column(snRgNme))] 'Alternative using Spreadsheet Functions to avoid having to VBA ()(  ) after the Evaluate
    329   '
    330   Dim stpClm% ' Variable for Stop column Number               '  ( % is shorthand for As Long ..http://www.excelforum.com/showthread.php?t=1116127&p=4256569#post4256569
    340   Let stpClm = sClm + (Cs - 1)             ' = 5
    350   ' [ ]
    360   Let stpClm = [column(A1:E10)]()(1) + ([columns(A1:E10)] - 1)
    370   Let stpClm = [column(snRgNme)]()(1) + ([columns(snRgNme)] - 1)
    380   ' In between step [ ] and Evaluate(" ")
    390   Let stpClm = [column(snRgNme)]()(UBound([column(snRgNme)]))
    400   ' Now Full Evaluate(" ")
    410   Let stpClm = Evaluate("column(snRgNme)")(1) + (Evaluate("columns(snRgNme)") - 1)
    420   Let stpClm = Evaluate("column(snRgNme)")(UBound(Evaluate("column(snRgNme)")))
    421
    430   Let stpClm = Evaluate("=MIN(column(snRgNme))") + (Evaluate("columns(snRgNme)") - 1) ''Alternatives using Spreadsheet Functions to avoid having to VBA ()(  ) after the Evaluate
    431   Let stpClm = [=MIN(column(snRgNme))] + ([columns(snRgNme)] - 1)
    432   Let stpClm = [=MIN(column(snRgNme)) + (columns(snRgNme) - 1)]
    439  '
    440   ' Start, Count and Stop of rows in an Area of contiguous cells in a Spreadsheet
    450   Dim Rs As Long 'Rows Count
    460   Let Rs = Evaluate("rows(snRgNme)")
    470   Let Rs = [rows(snRgNme)]
    480   Let Rs = [rows(A1:E10)]
    490                                                               Let vTemp = Evaluate("row(snRgNme)") ' = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}
    500   Dim sRw As Long 'Start Row
    510   Let sRw = Evaluate("row(A1:E10)")(1, 1) 'Note a 2 Dimensional,  1 column, "vertical" Array is returned : ' vTemp = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}
    520   Let sRw = Evaluate("row(snRgNme)")(1, 1)
    530   Let sRw = [row(A1:E10)]()(1, 1)
    540   Let sRw = [row(snRgNme)]()(1, 1)
    541
    550   Let sRw = Evaluate("=MIN(Row(snRgNme))"): Let sRw = [=MIN(Row(snRgNme))] '''Alternatives using Spreadsheet Functions to avoid having to VBA ()(  ) after the Evaluate
    560
    570   Dim stpRw% 'Stop Row
    580   Let stpRw = sRw + (Rs - 1)
    590   Let stpRw = [row(A1:E10)]()(1, 1) + ([rows(A1:E10)] - 1)
    600   Let stpRw = [row(snRgNme)]()(1, 1) + ([rows(snRgNme)] - 1)
    610  '
    620   Let stpRw = [row(snRgNme)]()(UBound([row(snRgNme)], 1), 1) 'UBound([row(snRgNme)], 1) is Ubound first ( "row" ) dimension.  UBound([row(snRgNme)], 2) would be the second dimension ( "column" ) count
    630  '
    640   Let stpRw = Evaluate("row(snRgNme)")(1, 1) + (Evaluate("rows(snRgNme)") - 1)
    650   Let stpRw = Evaluate("row(snRgNme)")(UBound(Evaluate("row(snRgNme)")), 1)
    660  '
    670   Let stpRw = Evaluate("=MIN(Row(snRgNme))") + (Evaluate("rows(snRgNme)") - 1) ''''Alternatives using Spreadsheet Functions to avoid having to VBA ()(  ) after the Evaluate
    680   Let stpRw = [=MIN(Row(snRgNme))] + [rows(snRgNme)] - 1
    690   Let stpRw = [=MIN(Row(snRgNme))] + [rows(snRgNme)] - 1
    700   Let stpRw = [=MIN(Row(snRgNme)) + rows(snRgNme) - 1]
    End Sub
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Testing Concatenating with styles
    By DocAElstein in forum Test Area
    Replies: 2
    Last Post: 12-20-2020, 02:49 AM
  2. testing
    By Jewano in forum Test Area
    Replies: 7
    Last Post: 12-05-2020, 03:31 AM
  3. Replies: 18
    Last Post: 03-17-2019, 06:10 PM
  4. Concatenating your Balls
    By DocAElstein in forum Excel Help
    Replies: 26
    Last Post: 10-13-2014, 02:07 PM
  5. Replies: 1
    Last Post: 12-04-2012, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •