none
Variable, I am trying to figure this out if can be done RRS feed

  • Question

  • I have in excel the variable name in one column “C” and the value for that variable in column “B”

    I need to copy the variable name as a variable name and then I want to assign the value to this variable

    Can this be done?

    Kosta

     

                    A                                                                             B                                            C

    Pricing

    20

    adrqPRfir

    Pricing end

    48

    adrqPRlas

    Quantities of garment

    65

    adrqQGfir

    Quantities of garment end

    92

    adrqQGlas

    Print cost

    103

    adrqPCfir

    Print cost end

    127

    adrqPClas

    Silk Screen Prices

    265

    adrqSSPfrs

    Silk Screen Prices end

    273

    adrqSSPlas


    Vforum

    Sunday, November 10, 2019 4:48 PM

Answers

  • Thank you. I am afraid we're not getting further. Sorry.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Kosta si Tuesday, November 12, 2019 6:43 PM
    Tuesday, November 12, 2019 5:00 PM
  • The code that I posted will ensure that the first worksheet in the workbook is named Sheet1.

    So if someone has renamed the first (leftmost) worksheet to ThisSheet or ThatSheet or OtherSheet, those lines of code will rename it to Sheet1.

    If that is not what you want, please explain.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by Kosta si Wednesday, August 19, 2020 9:18 PM
    Wednesday, August 19, 2020 8:59 PM
  • If you want to make sure that the code looks at the first (leftmost) sheet in wbDS, you can use

    lastrowDS = wbDS.Worksheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row

    This refers to the first sheet regardless of its name.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by Kosta si Thursday, August 20, 2020 12:49 PM
    Thursday, August 20, 2020 6:22 AM
  • See FixWorksheet.xlsm

    Regards, Hans Vogelaar (https://www.eileenslounge.com)


    Tuesday, August 25, 2020 11:58 AM

All replies

  • You cannot do that in VBA, but if you explain how you want to use that variable, we may be able to suggest a workaround.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 10, 2019 7:56 PM
  • Hi Hans,

    I don’t know where to start.

    I have created I in excel a pricing and an invoicing system back in 2007 with a data base SQL that was stored in my web hosting site, as you know at that time was not a cloud storage that was offered to the public.

    This give the ability to me or any other salesperson out in the field to be able to have access to the database that has company information, contact information and all the goods we sell in our case is (garments) with the cost prices include. The salesperson was able to do an a estimate at the customers site and if an item was not in the date base the office will fine out from a vendor and update the data base and the sales person will have the info almost instantaneously

    So my excel has couple sheets, I name them as “work Sheet’ is the place where the estimates was done, “invoice” sheet where the invoice, or the quote  was generated, “info Sheet” in this sheet was all the information was gathered from data base and has the pricing cost for UPS and many other costs what you my called a scratch pad.

    As you know times change and I change also.

    I decided to update this excel workbook as I am not using to much but is a good back up tool for me double check my estimating.

    And the focus is in the “info sheet”

    They are many sections a sloppily done at the time that I do not needed or is a better way doing it.

    I start writing a new code in VBA this time not in formulas as was done in the pass.

    Then I start deleting thing in “info Sheet” that did not go well with my coding becouse I had fix locations that VBA was looking into or writing into the “info Sheet” to work around this problem I created beginning and end of each section by inserting a label on the beginning and end of each section.

    That works becouse before the code start the routine finds the location of the information that is looking for and dos what must do.

    This forced me to create routine in each section that will first has to fine the location of the section and the do what ever must do the program began to be too long.

    I figure out if I do this once in the beginning of the program gat all the location addresses stored in the info sheet the I do not have to this work in each section of the programing also this gives me the ability to add a section by entering it in the section in “info Sheet” where all the section information are, the program will fine the beginning and the end and there we go.

    In the program I am already using all those variables the only thing I need to do is bring in the variable name and the value and of we go.

    If not, I must right another loop routing to get all that information in, and I think that is what I have to do.

    Sorry for the long explanation.  


    Vforum

    Monday, November 11, 2019 10:51 AM
  • Hans

    i have this.

    the question is can i increment the val1 to val2 and val3..... in  line 4 every time the loop goes around.

    let me know please

    thank you.

    Kosta 

     For xp = 1 To LrstRflsec
        Valinfo = "val" & xp
        If "Val" & xp = wksin.Range("C" & FrstA) Then
        Val1 = wksin.Range("B" & FrstA)
        End If

    next xp


    Vforum

    Monday, November 11, 2019 12:35 PM
  • Perhaps you can use the Collection object.

    1) At the top of a module, declare a Collection variable:

    Public col As New Collection

    2) Use a macro to populate the collection:

    Sub FillCol()
        Dim w As Worksheet
        Dim r As Long
        Dim m As Long
        Set w = Worksheets("...") ' Substitute the name
        m = w.Range("C1").End(xlDown).Row
        For r = 1 To m
            col.Add Item:=w.Range("B" & r).Value, Key:=w.Range("C" & r).Value
        Next r
    End Sub

    3. You can now use - for example - col("adrqQGLas") to get the number 92, or col("adrqSSPfrs") to get the number 265.

    Instead of a literal string "adrqQGLas", you can use a variable or the value of a cell.

    Does that help?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 11, 2019 1:26 PM
  • Hans

    I am getting an error “Run-time error ‘24’: object required in line 7

     col.Add Item:=wksin.Range("B" & r).Value, Key:=wksin.Range("C" & r).Value

    I assumed becouse row 1 for “B” and” C” there is no value I start the loop from 2 but this did not help

    Where is my mistake

    Kosta

     

    Sub FillCol()

        Dim w As Worksheet

        Dim r As Long

        Dim m As Long

        'Set w = Worksheets("info sheet") ' Substitute the name

        Set wksin = Sheets("Info Sheet") 'assigning sheet13 to wksIN

        m = wksin.Range("C1").End(xlDown).Row

        For r = 2 To m

            col.Add Item:=wksin.Range("B" & r).Value, Key:=wksin.Range("C" & r).Value

        Next r


    Vforum

    Monday, November 11, 2019 3:51 PM
  • How about

    Sub FillCol()
        Dim w As Worksheet
        Dim r As Long
        Dim m As Long
        Set w = Worksheets("...") ' Substitute the name
        m = w.Range("C1").End(xlDown).Row
        For r = 1 To m
            If w.Range("C" & r).Value <> "" Then
                col.Add Item:=w.Range("B" & r).Value, Key:=w.Range("C" & r).Value
            End If
        Next r
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 11, 2019 3:57 PM
  • same error

    Sub FillCol()
        Dim w As Worksheet
        Dim r As Long
        Dim m As Long
        Set w = Worksheets("info sheet") ' Substitute the name
        m = w.Range("C1").End(xlDown).Row
        For r = 1 To m
            If w.Range("C" & r).Value <> "" Then
                col.Add Item:=w.Range("B" & r).Value, Key:=w.Range("C" & r).Value
            End If
        Next r
    End Sub


    Vforum

    Monday, November 11, 2019 4:21 PM
  • the first go around is find when "r" is 1 

    the second go around when "r' 2 gives me that re when executes the next line

    Kosta 


    Vforum

    Monday, November 11, 2019 4:28 PM
  • In your screenshot at the top of this thread, column C contains text strings. Does that hold for the actual contents of column C on the info sheet worksheet? Or are there numbers among them?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 11, 2019 6:31 PM
  • this is it.


    Vforum

    Monday, November 11, 2019 7:42 PM
  • When I create a sheet like yours, the code runs without error.

    Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 11, 2019 9:21 PM
  • sorry for the delay

    there a link to the file

    https://1drv.ms/x/s!ApL41UVs1W9k1h63nNuvETzdvYe-?e=wgAeUY

    let me if you got it.

    Kostatest 110119-A


    Vforum

    Tuesday, November 12, 2019 1:58 AM
  • You forgot to declare the variable col at the top of the module.

    The code should look like this:

    Dim col As New Collection
    
    Sub FillCol()
        Dim w As Worksheet
        Dim r As Long
        Dim m As Long
        Set w = Worksheets("info sheet")
        m = w.Range("C1").End(xlDown).Row
        For r = 1 To m
            If w.Range("C" & r).Value <> "" Then
            col.Add Item:=w.Range("B" & r).Value, Key:=w.Range("C" & r).Value
            End If
        Next r
    End Sub
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, November 12, 2019 9:54 AM
  • sorry i just copy and paste the code did not realized that was missing.

    Thank you very much

    Kosta


    Vforum

    Tuesday, November 12, 2019 10:07 AM
  • I mentioned it in the post with the code:

    "1) At the top of a module, declare a Collection variable:

    Public col As New Collection"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, November 12, 2019 10:14 AM
  • Hans sorry i am bothering you with,

    i did it and i am not getting any error now but i stil not understanding how this is working

    if i use this variables now they should have the values i asume but how do i do it.

    can you please update the file that i send you so i can see how it works.

    so each variable has the proper value 

    I would appreciated

    thank you.

    Kosta

    by the way what is http://www.eileenslounge.com i did visit tis site is the same as this forum?


     

    Vforum

    Tuesday, November 12, 2019 10:39 AM
  • The code doesn't create variables as in your first post; that is not possible - VBA doesn't work that way.

    Instead, after running the FillCol macro, you can retrieve the number associated with a value such as adrqQGLas from the collection by using col("adrqQGLas").

    Currently, there is no other code in the workbook, so I don't know how you want to use it.

    If you explain how you wanted to use the variables, I may be able to provide a more detailed example.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, November 12, 2019 11:24 AM
  • As you see in my table, I have the beginning and the and position of each section next to each section name.

    That did get generate by a loop reading the column “A” finding the row number and the posting it in column “B” the next step is to assign those number to the variables in column “C”.

    I know the name of the variable ad I know the value how can I say in VBA variable x has the value xx without typing all those variable in VBA or even if I created a loop to read the variables in “info Sheet” and get the value how to assign this value to variable x

    That is what I am trying to do.

    Kosta

      

    Vforum

    Tuesday, November 12, 2019 11:48 AM
  • But how do you want to use those variables?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, November 12, 2019 12:08 PM
  • Those variables are used in the program now for loop control and putting found values to a specific section.

    If I want to find how many garments used for each say screen (printing screen) then I have a section that I store this information.

     in the beginning of this loop section I am running another code to fine the beginning and the of the section where the data information is and as you see they are 4 so far and may add few more as I ma working on this program typical routine finding the first and last row of the section is below.

    As I mentioned before I like to do this once in the beginning of the program assign all the values to the variables and no to worry after that.

    If add a new section I just type the section name in the beginning of “info sheet” assign the variable name and the program does the rest and I do not have to add these codes every time.


    Vforum

    Tuesday, November 12, 2019 12:38 PM
  • Thank you. I am afraid we're not getting further. Sorry.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Kosta si Tuesday, November 12, 2019 6:43 PM
    Tuesday, November 12, 2019 5:00 PM
  • that is ok 

    thank you again

    Kosta


    Vforum

    Tuesday, November 12, 2019 6:43 PM
  • Feel free to come back to this thread if you have more detailed information to share.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, November 12, 2019 8:26 PM
  • I have in my active life program a multi-bank of elevators over 60 floors high risers and very high speeds and the only program I had at that time was machine language, once I muster the commands was downhill from there and on.

    I just have to master the VBA commands that’s all.

    Anyway, you were very helpful thank you very much.

    Kosta  

     

    Vforum

    Tuesday, November 12, 2019 8:39 PM
  • is any way to jump to a section of the code from say index type setup example.

    '1-finding the cost of garments

    '2-finding the cost of screens

    '3-finding the cost of printing

    let me know please.

    thank you.

    Kosta


    Vforum

    Wednesday, November 13, 2019 12:12 PM
  • One way to do this would be to insert labels into the code:

    Sub Test()
        …
        …
        If condition Then
            GoTo Garments
        ElseIf othercondition Then
            GoTo Screens
        Else
            GoTo Printing
        End If
        …
        …
    Garments:
        …
        GoTo ExitHere
    Screens:
        …
        GoTo ExitHere
    Printing:
        …
    ExitHere:
    End Sub

    Another (more elegant) way would be to create separate macros for garments, screens and printing, and to call those from your main macro when needed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 13, 2019 12:34 PM
  • the second one is a better idea

    so i can set all the variables in the first micro and that information will be transferred to the micro that is called upon is that true?

    Kosta

     

    Vforum

    Wednesday, November 13, 2019 12:38 PM
  • is any way in vba to identify that a cell has a border.

    i am trying to two addresses of a row the first row in column "A" that has a border and the last row that has a border.

    I am very sure this can be done.

    let me know please.

    Kosta

     

    Vforum

    Wednesday, November 13, 2019 1:07 PM
  • There are two ways to share variables between macros:

    1) Declare them as public at the top of the module, instead of inside the macros. For example:

    Public n As Long
    
    Sub Test1()
        ' Assign a value to n
        n = 37
        ' Call another macro
        Call Test2
    End Sub
    
    Sub Test2
        ' Use the value of n
        MsgBox n
    End Sub

    2) Pass the value(s) you need as arguments to a procedure:

    Sub Test1()
        Dim n As Long
        ' Assign a value to n
        n = 37
        ' Call another procedure and pass n
        Call Test2(n)
    End Sub
    
    Sub Test2(x As Long)
        ' Use the value of x
        MsgBox x
    End Sub

    When you call Test2. the variable n with value 37 will be passed to Test2. where it can be used as the variable x. So the message box in Test2 will display 37.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 13, 2019 1:10 PM
  • thank you,

    Kosta


    Vforum

    Wednesday, November 13, 2019 2:42 PM
  • Good Morning Hans,

    I am trying to find the first row and the last row address that has a border around the cell in column “A”

    can you help.

    Kosta 

    Sub border()
    Set wksws = Sheets("Work Sheet") 'assigning sheet1 to wksWS
    If wksws.Range("A45:A50").Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
    borT = wksws.Range("A45:A50").Borders(xlEdgeTop).LineStyle <> xlLineStyleNone
    MsgBox "Yes it does border"
    End If

    If wksws.Range("A45:A50").Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
    borB = wksws.Range("A45:A50").Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone
    MsgBox "Yes it does border"
    End If
    End Sub


    Vforum

    Thursday, November 14, 2019 10:26 AM
  • Does this do what you want?

    Sub Border()
        Dim wksws As Worksheet
        Dim r As Long
        Dim rt As Long
        Dim rb As Long
        Set wksws = Worksheets("Work Sheet")
        r = 1
        Do While wksws.Range("A" & r).Borders(xlEdgeTop).LineStyle = xlLineStyleNone
            r = r + 1
        Loop
        rt = r
        Do While wksws.Range("A" & r + 1).Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone
            r = r + 1
        Loop
        rb = r
        MsgBox "Borders from row " & rt & " to " & rb, vbInformation
    End Sub

    rt is the first row with a top border.

    rb is the last row with a bottom border.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 14, 2019 12:20 PM
  • perfect thank you,

    Kosta


    Vforum

    Thursday, November 14, 2019 12:54 PM
  • Hans

    let me understand this "r" gets incremented as longest there is a row with border is that true.

    and if yes what will happened if was a row without a border in between.

    this question is for me to understand what you give me is doing the job fine.

    just asking.

    Kosta



    Vforum

    Thursday, November 14, 2019 1:46 PM
  • Good morning Hans

    Is any way to short this?

    wksin.Range("M" & priceinfo) = wksin.Range("I" & priceinfo) + wksin.Range("J" & priceinfo) + wksin.Range("K" & priceinfo) + wksin.Range("L" & priceinfo)

    I try this but doesn’t work.

    wksin.Range("M" & priceinfo)  = WorksheetFunction.Sum(wksin.Range("I" & priceinfo & ":L" & priceinfo).Value)

    Thank you

    Kosta


    Vforum

    Friday, November 15, 2019 9:20 AM
  • let me understand this "r" gets incremented as longest there is a row with border is that true.

    and if yes what will happened if was a row without a border in between.

    1. Yes, that is correct

    2. If there was a row in between without a border, the code would stop there. If that is a problem, we would need a different approach. Let me know if you want that.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 15, 2019 11:23 AM
  • It should be like this:

    wksin.Range("M" & priceinfo)  = WorksheetFunction.Sum(wksin.Range("I" & priceinfo & ":L" & priceinfo))

    i.e. without .Value after the range. This is because the Sum function expects a range.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 15, 2019 11:27 AM
  • no i am good with this.

    just learning.

    thank you

    Kosta


    Vforum

    Friday, November 15, 2019 12:38 PM
  • yeas it's good now

    Thank you

    Kosta


    Vforum

    Friday, November 15, 2019 12:42 PM
  • Hi Hans

    I need to go a specific cell in a work sheet

    the range method is not working properly

    the cells method is working but i need to directed to work sheet "wksws" from info sheet so when I do wksws.range(.cells(al,bl)).select is not working.

    I think i am tired cannot think anymore.

    Kosta 

    Sub input1()
        Set wksws = Sheets("Work Sheet") 'assigning sheet6 to wksWS
        Set wksin = Sheets("Info Sheet") 'assigning sheet13 to wksIN
        al = 16
        bl = 15
       
        wksws.Range("bl:al").Select
        Cells(al, bl).Select
       
       
    End Sub


    Vforum

    Saturday, November 16, 2019 7:18 PM
  • You can use Application.GoTo:

    Sub input1()
        Dim al As Long
        Dim bl As Long
        Dim wksws As Worksheet
        Set wksws = Sheets("Work Sheet") 'assigning sheet6 to wksWS
        al = 16
        bl = 15
        Application.GoTo wksws.Cells(al, bl)
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2019 8:25 PM
  • thank you,

    what i am trying to do is replace the "O" with a variable so i can go O,P,Q,R ,

    so i can get the values of each cell without repeating the code for each column

    Kosta

    If Not (wksws.Range("A" & artquan) = "" Or wksws.Range("O" & artquan) = "E") Then


    Vforum

    Sunday, November 17, 2019 10:08 AM
  • Perhaps

        Dim col As Long
        For col = 15 To 18 ' columns O to R
            ' use wksws.Cells(artquan, col) here
            ...
        Next col


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 17, 2019 11:53 AM
  • So far so good.

    Now once I found what I need before gets rewriting I need to do something and then get back, in the old day was something like got sub after executes the sub then will return to the same spot and continued.

    I thought I can Crete another sub under this one I called “calculate”.

    When the program runs It does call the subroutine and goes there but all the variables has no values

    Is any other way doing this?

    Kosta

       

     

    For col = 15 To 18 ' columns O to R

                    If Not (wksws.Range("A" & artquan) = "" Or wksws.Cells(xfstrwsaj, col) = "E") Then 'screequnt = wksws.Range("O" & x)

               

                    screequnt = wksws.Cells(xfstrwsaj, col)

                   Call Calculate

                    End If

                Next col

     

    Sub Calculate()

            OCLVNTCO = wksin.Range("A" & adq61312ffir).Offset(0, screequnt).Value 'cost "OCLVNTCO" "one color large varsity nylon transfer cost" in "info sheet"

            OCLVNSUC = wksin.Range("A" & adq61312ffir).Offset(0, screequnt + 1).Value 'setup value "OCLVNSUC" "one color large varsity nylon Setup cost" in "info sheet"

            OCLVNxpt = OCLVNTCO + (OCLVNSUC / OCLVNGT)

            MsgBox "$" & OCLVNxpt & " " & "Per Transfer"

     

    End Sub


    Vforum

    Sunday, November 17, 2019 3:42 PM
  • If you want to share variables between different procedures (Subs), you can declare them at the top of the module, above the Subs:

    Dim wksws As Worksheet
    Dim wksin As Worksheet
    Dim screequnt As Long
    
    Sub …
        ...
    End Sub
    
    Sub Calculate()
        ...
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 17, 2019 5:06 PM
  • I put them above the first sub

    do i have to put it also above calculate sub?

    becouse is not working

    or I misunderstood something

    Kosta

    Dim wksws, wkswin As Worksheet
    Dim screenqunt As Long
    Dim OCLVNTCO As Long
    Dim OCLVNSUC As Long
    Dim OCLVNFCO As Long

    _________________________________________________________________________

    Sub printcost()
       
        Dim r As Long
        Dim AJTO As Long
        Dim AJBO As Long
        Dim adrqPRfir As Long
        Dim adrqPRlas As Long

    ……...

    end sub

    ______________________________________________________________

    Sub Calculate()
           
            If screenqunt = 1 Then
                    ElseIf screenqunt = 2 Then
                    screenqunt = screequnt + 7
                    ElseIf screenqunt > 2 Then
                    screenqunt = screenqunt + 11
            End If
           
            OCLVNTCO = wksin.Range("A" & adq61312ffir).Offset(0, screenqunt).Value 'cost "OCLVNTCO" "one color large varsity nylon transfer cost" in "info sheet"
            OCLVNSUC = wksin.Range("A" & adq61312ffir).Offset(0, screenqunt + 1).Value 'setup value "OCLVNSUC" "one color large varsity nylon Setup cost" in "info sheet"
            OCLVNFCO = OCLVNTCO + (OCLVNSUC / OCLVNGT) '"OCLVNFCO" "one color large varsity nylon Final cost" "OCLVNGT" "one color large varsity nylon garment total"
            MsgBox "$" & OCLVNxpt & " " & "Per Transfer"
    End Sub


     

     

    Vforum

    Sunday, November 17, 2019 8:32 PM
  • Do you get an error message? If so, what does it say?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 17, 2019 8:44 PM
  • Yes, I get an error “RUN-TIME ERROR ‘424’: Object required” when goes to the line

    I declare also the “adq61312ffir” as DIM

    as you see in my local window all the variables are empty

    OCLVNTCO = wksin.Range("A" & adq61312ffir).Offset(0, screenqunt).Value 'cost "OCLVNTCO" "one color large varsity nylon transfer cost" in "info sheet"


    Vforum

    Sunday, November 17, 2019 9:04 PM
  • also the variables that i amusing they are not handling the decimals wheel

    OCLVNTCO supposed to have the value.15 is showing empty

    do i have to declare those variables deferent then As Long   

    (OCLVNSUC / OCLVNGT) supposed to be 0.769 if your round up should be 0.77 but is coming as 1

    OCLVNTCO = wksin.Range("A" & adq61312ffir).Offset(0, screenqunt).Value 'cost "OCLVNTCO" "one color large varsity nylon transfer cost" in "info sheet"
            OCLVNSUC = wksin.Range("A" & adq61312ffir).Offset(0, screenqunt + 1).Value 'setup value "OCLVNSUC" "one color large varsity nylon Setup cost" in "info sheet"
            OCLVNFCO = OCLVNTCO + (OCLVNSUC / OCLVNGT) '"OCLVNFCO" "one color large varsity nylon Final cost" "OCLVNGT" "one color large varsity nylon garment total"
            MsgBox "$" & OCLVNFCO & " " & "Per Transfer"

     

    Vforum

    Sunday, November 17, 2019 9:20 PM
  • Do you set wksin to a worksheet somewhere before the line that causes the error?

    If you want variables to have decimals, you should declare them as Double, not as Long. The Long data type can hold only whole numbers.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 17, 2019 9:44 PM
  • Yes about 52 lines after the first sub “sub printcost()” do I have to set them also on the top before sub printcost()


    Vforum

    Sunday, November 17, 2019 9:51 PM
  • the numbers worked but now I have 15 decimal points I just need two decimal points 

    Vforum

    Sunday, November 17, 2019 9:59 PM
  • You must set wksin somewhere before the first time you use it. The same holds for wksws.

    About the decimal places: you could - for example - change

    OCLVNFCO = OCLVNTCO + (OCLVNSUC / OCLVNGT)

    to

    OCLVNFCO = Round(OCLVNTCO + (OCLVNSUC / OCLVNGT), 2)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 17, 2019 10:08 PM
  • I believe it's in correct spot set is been used before any used any place else

    also is any way to make it better  Diming all thosevariables  

    Dim wksws, wkswin As Worksheet
    Dim screenqunt As Long
    Dim OCLVNTCO As Double
    Dim OCLVNSUC As Double
    Dim OCLVNFCO As Double
    Dim adq61312ffir As Long

    _________________________________________________________________________________________
    Sub printcost()
       
        Dim r As Long
        Dim AJTO As Long
        Dim AJBO As Long
        Dim adrqPRfir As Long
        Dim adrqPRlas As Long
        Dim adrqQGfir As Long
        Dim adrqQGlas As Long
        Dim adrqPCfir As Long
        Dim adrqPClas As Long
        Dim adrqSSPfir As Long
        Dim adrqSSPlas As Long
        Dim adqPMfir As Long
        Dim adqPMlas As Long
        Dim adq61312ffir As Long
        Dim adq61312flas As Long
        Dim adq613GSfir As Long
        Dim adq613GSlas As Long
        Dim adq613GMfir As Long
        Dim adq613GMlas As Long
        Dim adq613GLfir As Long
        Dim adq613GLlas As Long
        Dim adq613GXLfir As Long
        Dim adq613GXLlas As Long
        Dim adq613GSSTfir As Long
        Dim adq613GSSTlas As Long
        Dim adq613GMSTfir As Long
        Dim adq613GMSTlas As Long
        Dim adq613GLSTfir As Long
        Dim adq613GLSTlas As Long
        Dim adq613GXLSTfir As Long
        Dim ssprmat As Long
        Dim prcmat As Long
        Dim profmat As Long
        Dim xp As Long
        Dim profitinfo As Long
        Dim ROPQR As Long
        Dim COPQR As Long
        Dim col As Long
        Dim col2 As Long
        Dim screenqunt As Long
       
       
        profitinfo = InputBox("input the profit margin ", "Profit Margin")
       
        'Disable certain Excel Features while macro is runing
        'Application.Calculation = xlCalculationManual 'stoping automatic calculation
        'Application.EnableEvents = False
        'Application.ScreenUpdating = False 'stoping screen updating
        Set wksws = Sheets("Work Sheet") 'assigning sheet6 to wksWS
        Set wksin = Sheets("Info Sheet") 'assigning sheet13 to wksIN


    Vforum

    Sunday, November 17, 2019 10:16 PM
  • If you really need all those variables, then you have to declare all of them.

    It's hard to comment on your code because I see only parts of it.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 17, 2019 10:28 PM
  • you have the whole code

    where I am working is in line 308

    I have done something temporarily with goto until i can salve this issue

    Dim wksws, wkswin As Worksheet Dim screenqunt As Long Dim OCLVNTCO As Double Dim OCLVNSUC As Double Dim OCLVNFCO As Double Dim adq61312ffir As Long Sub printcost() Dim r As Long Dim AJTO As Long Dim AJBO As Long Dim adrqPRfir As Long Dim adrqPRlas As Long Dim adrqQGfir As Long Dim adrqQGlas As Long Dim adrqPCfir As Long Dim adrqPClas As Long Dim adrqSSPfir As Long Dim adrqSSPlas As Long Dim adqPMfir As Long Dim adqPMlas As Long Dim adq61312ffir As Long Dim adq61312flas As Long Dim adq613GSfir As Long Dim adq613GSlas As Long Dim adq613GMfir As Long Dim adq613GMlas As Long Dim adq613GLfir As Long Dim adq613GLlas As Long Dim adq613GXLfir As Long Dim adq613GXLlas As Long Dim adq613GSSTfir As Long Dim adq613GSSTlas As Long Dim adq613GMSTfir As Long Dim adq613GMSTlas As Long Dim adq613GLSTfir As Long Dim adq613GLSTlas As Long Dim adq613GXLSTfir As Long Dim ssprmat As Long Dim prcmat As Long Dim profmat As Long Dim xp As Long Dim profitinfo As Long Dim ROPQR As Long Dim COPQR As Long Dim col As Long Dim col2 As Long Dim screenqunt As Long profitinfo = InputBox("input the profit margin ", "Profit Margin") 'Disable certain Excel Features while macro is runing 'Application.Calculation = xlCalculationManual 'stoping automatic calculation 'Application.EnableEvents = False 'Application.ScreenUpdating = False 'stoping screen updating Set wksws = Sheets("Work Sheet") 'assigning sheet6 to wksWS Set wksin = Sheets("Info Sheet") 'assigning sheet13 to wksIN '********************************************************************************************************************************* '*Finding the beginning and the end of the rows where item information entered for columns "A" through "AJ" in sheet "Work sheet"* '********************************************************************************************************************************* r = 1 Do While wksws.Range("A" & r).Borders(xlEdgeTop).LineStyle = xlLineStyleNone 'finding the beginning row that has border in column "A" in "work sheet" r = r + 1 Loop AJTO = r - 1 Do While wksws.Range("A" & r + 1).Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone 'finding the end row that has border in column "A" in "work sheet" r = r + 1 Loop AJBO = r '**************************************************************************************************************** 'finding the first and last what row has art data identifier "P-...", "N", "R", & "W" for artwork in "work Sheet* '*And finding the last row for calumn "A" in sheet "info sheet" * '**************************************************************************************************************** fstrwsaj = wksws.Range("AJ" & AJTO & ":AJ" & AJBO).Find("*", SearchDirection:=xlNext, SearchOrder:=xlByRows, LookIn:=xlValues).Row 'finds the first row that have date in the garment totals in "work Sheet" in column "AJ" LstRwsAJ = wksws.Range("AJ" & AJTO & ":AJ" & AJBO).Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row 'finds the last row that have date in the garment totals in "work Sheet" in column "AJ" LastRin = wksin.Range("A" & Rows.Count).End(xlUp).Row 'finding last row in "Info Sheet" column "A" '************************************************************************************************************************************* '*In this section we are finding the beginning and the end of the section in "info sheet" that has all the section headings * '*Then using the heading information from each section, we find the first and last row for each section and storing this Information * '*in column "B" in "info sheet". After that we are assigning the values to its variable for more detail what each variable represents* '*go to "info Sheet" row's "A1" to rows "C..." * '************************************************************************************************************************************* '*Finding the first and last row where the row information for each section stored in sheet "info sheet"* '******************************************************************************************************** Set rng = wksin.Range("A1:A" & LastRin).Find(what:="First and lats row's if each section", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, lookat:=xlWhole, LookIn:=xlValues) 'finding the bigining row "Quantities of garment" If Not (rng Is Nothing) Then FrstRflsec = rng.Row 'if "Quantities of garment" found then copy the information to "adrQ"" FrstRflsec = FrstRflsec End If Set rng = wksin.Range("A1:A" & LastRin).Find(what:="First and lats row's if each section end", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, lookat:=xlWhole, LookIn:=xlValues) 'finding the bigining row "Quantities of garment end" If Not (rng Is Nothing) Then LrstRflsec = rng.Row 'if "quantities of garment end" found then copy the information to "adrE"" LrstRflsec = LrstRflsec - 1 End If '******************************************************************************************************** FrstRflsecA = FrstRflsec wksin.Range("B" & FrstRflsec & ":B" & LrstRflsec) = "" 'Clearing column "B" before storing the first and last row location from each section For SectionLoc = 1 To LrstRflsec 'start the loop FrstRflsecA = FrstRflsecA + 1 FrstA = FrstRflsecA nameofsect = wksin.Range("A" & FrstRflsecA) FrstRflsecA = FrstRflsecA + 1 FrstB = FrstRflsecA nameolsect = wksin.Range("A" & FrstRflsecA) '******************************************************************************************************** '*Finding the first and last row where the row information stored for each section in sheet "info sheet"* '******************************************************************************************************** Set rng = wksin.Range("A" & LrstRflsec & ":A" & LastRin).Find(what:=nameofsect, SearchDirection:=xlPrevious, SearchOrder:=xlByRows, lookat:=xlWhole, LookIn:=xlValues) 'Finding the beginning of search section If Not (rng Is Nothing) Then Frst = rng.Row 'if "Quantities of garment" found then copy the information to "adrQ"" Frst = Frst + 1 End If Set rng = wksin.Range("A" & LrstRflsec & ":A" & LastRin).Find(what:=nameolsect, SearchDirection:=xlPrevious, SearchOrder:=xlByRows, lookat:=xlWhole, LookIn:=xlValues) 'Finding the End of search section If Not (rng Is Nothing) Then Lrst = rng.Row 'if "quantities of garment end" found then copy the information to "adrE"" Lrst = Lrst - 1 End If wksin.Range("B" & FrstA) = Frst wksin.Range("B" & FrstB) = Lrst '********************************************************************************************************************************* '*Assigning the values to its variable for more detail what each variable represents go to "info Sheet" row's "A1" to rows "C..."* '********************************************************************************************************************************* If "adrqPRfir" = wksin.Range("C" & FrstA) Then adrqPRfir = wksin.Range("B" & FrstA) End If If "adrqPRlas" = wksin.Range("C" & FrstB) Then adrqPRlas = wksin.Range("B" & FrstB) End If If "adrqQGfir" = wksin.Range("C" & FrstA) Then adrqQGfir = wksin.Range("B" & FrstA) End If If "adrqQGlas" = wksin.Range("C" & FrstB) Then adrqQGlas = wksin.Range("B" & FrstB) End If If "adrqPCfir" = wksin.Range("C" & FrstA) Then adrqPCfir = wksin.Range("B" & FrstA) End If If "adrqPClas" = wksin.Range("C" & FrstB) Then adrqPClas = wksin.Range("B" & FrstB) End If If "adrqSSPfir" = wksin.Range("C" & FrstA) Then adrqSSPfir = wksin.Range("B" & FrstA) End If If "adrqSSPlas" = wksin.Range("C" & FrstB) Then adrqSSPlas = wksin.Range("B" & FrstB) End If If "adqPMfir" = wksin.Range("C" & FrstA) Then adqPMfir = wksin.Range("B" & FrstA) End If If "adqPMlas" = wksin.Range("C" & FrstB) Then adqPMlas = wksin.Range("B" & FrstB) End If If "adq61312ffir" = wksin.Range("C" & FrstA) Then adq61312ffir = wksin.Range("B" & FrstA) End If If "adq61312flas" = wksin.Range("C" & FrstB) Then adq61312flas = wksin.Range("B" & FrstB) End If SectionLoc = FrstB Next SectionLoc 'end of loop wksin.Range("E" & adqPMfir) = "" wksin.Range("E" & adqPMfir) = profitinfo / 100 '**************************************************************************************************** '*finds the beginning and end of where the garment quantities and the type of screen work are stored* '**************************************************************************************************** '*********************************************************************************************************************************************************** '*In this section we are identifying how many rows has the same screen designation "P-..." or "N" or "R" * '*Putting the information in the row "info sheet" "A" staring after the heading "quantities of garment" in descending order rows row 10,11,12??? and so on.* '*********************************************************************************************************************************************************** wksin.Range("A" & adrqQGfir, "K" & adrqQGlas) = "" 'clear all values in "Info sheet" range "A65:G92" art = adrqQGfir ds = adrqQGfir - 1 For xart = fstrwsaj To LstRwsAJ 'Strat of loop If Not wksws.Range("M" & xart) = 0 Then 'if cell is in the range "M15:M42" is not 0 or "" then If Not wksws.Range("M" & xart) = "W" Then ' if the screen designation is "W" then skip recording the screen designation artvalue = wksws.Range("M" & xart) 'transferring the art designation from column "M" in "work sheet" to "artvalue" in "info sheet" ("P-..." or "N" or "R") End If If wksws.Range("M" & xart) = artvalue Then 'If Range "M15:M42" in "work sheet" has any screen designation ("P-..." or "N", or "R") transferring that designatio to "callW" in "info sheet"" cellW = artvalue '"artvalue" is stored in "callW" until a new "artvalue" is introduced End If End If wksin.Range("A" & art) = cellW 'Transfering the screen designation ("P-..." or "N", or "R") to column "A" in "info sheet" '******************************************************************************************************************* '*In this section, totaling the number of garments under each screen designation from calumny "AJ" in "work sheet" * '*Putting the information in the section "quantities of garment" in "info sheet" * '*staring with column "C" where the artwork indication is placed and column "D" the total of garments is placed * '******************************************************************************************************************* If wksin.Range("A" & art) = artvalue Then 'checking if any screen designation ("P-..." or "N" or "R") matching the "artvalue" in column "A" in "info sheet" If Not artvaluex = artvalue Then 'checking to see if "artvaluex" is equal to "artvalue" artvaluex = artvalue 'transferring the value of "artvalue" to "artvaluex" total1 = 0 totalsum1 = 0 totallartsumD = 0 ds = ds + 1 End If total1 = wksws.Range("AJ" & xart) 'geting the total of garment from column "AJ" in "work sheet" totalsum1 = total1 + totalsum1 'totaling all the garment of all the rows from column "AJ" that is matching the screen designation wksin.Range("C" & ds) = artvalue 'Writing the designation for each type of a screen ("P-..." or "N", and "R" from column "M" in "work sheet" to a cell in column "C" In "Info sheet" wksin.Range("D" & ds) = totalsum1 'Writing the result of the total of all garments from "work sheet" column "AJ" for each art screen designation column "M" in "work sheet" "P-...","N", and "R" to a cell in column "D" In "Info sheet" End If '***************************************************************************************************************** '*In this section, totaling the number of new screens under designated "P-.." or "W" or "R" * '*Putting the information in the section with heading "quantities of garment" in "info shett" * '*staring with column "E" where the artwork designation is placed and column "F" the total of screens is placed * '*for artwork ("P-..."),"H" where the artwork designation is placed and column "I" the total of screens is placed * '*for artwork ("W"), and "J" where the artwork designation is placed and column "K" the total of screens is placed* '*for artwork ("R") * '****************************************************************************************************************** Set CriteriaRange = wksws.Range("O" & xart & ":R" & xart) 'finding the range for the screen information for column' "O" through "R" in "work sheet" SumVal = Application.WorksheetFunction.SumIf(CriteriaRange, "<>E") 'Totaling the screens in column' "O" through "R" in "work sheet" excluding the cells that have "E" and storing it in "SumVal" 'Set CriteriaRange = Nothing If Left(wksws.Range("M" & xart), 1) = "P" Then 'If the screen designation is ("P-...") then proceed wksin.Range("E" & ds) = artvalue 'Writing the designation for a screen "P-.." from column "M" in "work sheet" to a cell in column "E" In "Info sheet" wksin.Range("F" & ds) = SumVal 'Writing the total value of screens for the designation "P-..." from column "M" in "work sheet" to column "F" In "Info sheet" End If If Left(wksws.Range("M" & xart), 1) = "W" Then 'If the screen designation is ("W") then proceed wksin.Range("H" & ds) = wksws.Range("M" & xart) 'Writing the designation for a screen "W" from column "M" in "work sheet" to a cell in column "E" In "Info sheet" wksin.Range("I" & ds) = SumVal 'Writing the total value of screens for the designation "W" from column "M" in "work sheet" to column "F" In "Info sheet" End If If Left(wksws.Range("M" & xart), 1) = "R" Then 'If the screen designation is ("R") then proceed wksin.Range("J" & ds) = wksws.Range("M" & xart) 'Writing the designation for a screen "R" from column "M" in "work sheet" to a cell in column "E" In "Info sheet" wksin.Range("K" & ds) = SumVal 'Writing the total value of screens for the designation "W" from column "M" in "work sheet" to column "F" In "Info sheet" End If art = art + 1 Next xart 'loop incrimet '******************************************************************************************************** '*In this section copying the total garment information from column "D" in "info sheet" matching it with* '*same screen designation and then placing the information in column "B" in info sheet * '******************************************************************************************************** For xxart = adrqQGfir To adrqQGlas 'Strat of loop Set compld = wksin.Range("C" & adrqQGfir & ":C" & adrqQGlas).Find(what:=wksin.Range(("A" & xxart)).Value, LookIn:=xlValues, lookat:=xlWhole) 'identifying the value for cell on the column and storing it to "compld" wksin.Range("B" & xxart) = compld.Offset(, 1).Value ' storing the quantity of garmen to a cell Next xxart 'loop incriment '************************************************************************************************************************************************************** '*In this section checking in "print cost" data tables to find the closest row matching the quantity of garment that will printed for each screen designation * '*using the "match" function with -1 go to Greater than (the cost information has to be * '*arranged from the largest number to the smallest number) option, the other two option are 1 (lest then * '*the information has to be arranged from the smallest to the largest and 0 (exact match) * '************************************************************************************************************************************************************** wksin.Range("I" & adrqPRfir + 1 & ":O" & adrqPRlas) = "" 'clearing the cells where the pricing will stored "Info Sheet" "I" "L" artquan = fstrwsaj - 1 'transferring the first-row address from "AJ" in "work sheet" that has the garment total for that screen designation priceinfo = adrqPRfir 'Transferring the address of the first row in "info sheet" that has the pricing information this the section where the printing cost for each location it is stored For xt = adrqQGfir To adrqQGlas 'Starting the loop sequence going through each row where the garment quantiles are stored in section "Quantities of garment" in "info Sheet" If wksws.Range("K8") = "Screen printing" Then 'if cell "K8" in "work Sheet" is "Screen printing" the proceed if not go to the next artquan = artquan + 1 priceinfo = priceinfo + 1 prcmat = WorksheetFunction.Match(wksin.Range("B" & xt).Value, wksin.Range("A" & adrqPCfir & ":A" & adrqPClas), -1) 'Finding the range to lookup in "print cost" column "A" for the price of the screen according the quantity of the garment per screen for each location on the garment and storing it to variable "prcmat" If wksin.Range("g" & priceinfo) = 1 Then 'selecting print price for light color garments xp = prcmat ElseIf wksin.Range("g" & priceinfo) = 2 Then 'selecting print price for dark color garments xp = prcmat + 14 End If End If If wksws.Range("K8") = "613 Original one color 12.75 x 9 Varsity & Nylon" Then 'if cell "K8" in "work Sheet" is "Screen printing" the proceed if not go to the next artquan = artquan + 1 xadrqQGfir = adrqQGfir 'Transferring the beginning row address "adrqQGfir" from the section "Quantities of garment" to auxiliary variable "xadrqQGfir" "ininfo sheet" xfstrwsaj = fstrwsaj 'Transferring the beginning row address "fstrwsaj" from the "work sheet "where the information of the quantity of garment for each row stored " to auxiliary variable "xfstrwsaj" in "info sheet XadrqPRfir = adrqPRfir + 1 For OCLVNLO = fstrwsaj To LstRwsAJ '"OCLVNLO" "one color large varsity nylon loop" in "info sheet" If Left(wksin.Range("A" & xadrqQGfir), 1) = "P" Then OCLVNGT = wksin.Range("B" & xadrqQGfir) 'Finding the total quantity of garment to be printed for each artwork per row and transferring that information to auxiliary variable "OCLVNGT" "one color large varsity nylon garment total" in "info sheet" End If For col = 15 To 18 ' columns O to R If Not (wksws.Range("A" & artquan) = "" Or wksws.Cells(xfstrwsaj, col) = "E") Then 'screequnt = wksws.Range("O" & x) screenqunt = wksws.Cells(xfstrwsaj, col) GoTo Calculate: 'Call Calculate OCLVNloop: End If Next col Calculate: If screenqunt = 1 Then ElseIf screenqunt = 2 Then screenqunt = screequnt + 7 ElseIf screenqunt > 2 Then screenqunt = screenqunt + 11 End If OCLVNTCO = wksin.Range("A" & adq61312ffir).Offset(0, screenqunt).Value 'cost "OCLVNTCO" "one color large varsity nylon transfer cost" in "info sheet" OCLVNSUC = wksin.Range("A" & adq61312ffir).Offset(0, screenqunt + 1).Value 'setup value "OCLVNSUC" "one color large varsity nylon Setup cost" in "info sheet" OCLVNFCO = Round(OCLVNTCO + (OCLVNSUC / OCLVNGT), 2) '"OCLVNFCO" "one color large varsity nylon Final cost" "OCLVNGT" "one color large varsity nylon garment total" MsgBox "$" & OCLVNFCO & " " & "Per Transfer" For col2 = 9 To 12 wksin.Cells(XadrqPRfir, col2) = OCLVNFCO Next col2 GoTo OCLVNloop: 'If screenqunt = 1 Then 'ElseIf screenqunt = 2 Then 'screenqunt = screequnt + 7 'ElseIf screenqunt > 2 Then 'screenqunt = screenqunt + 11 'End If 'OCLVNTCO = wksin.Range("A" & adq61312ffir).Offset(0, screequnt).Value 'cost "OCLVNTCO" "one color large varsity nylon transfer cost" in "info sheet" 'OCLVNSUC = wksin.Range("A" & adq61312ffir).Offset(0, screequnt + 1).Value 'setup value "OCLVNSUC" "one color large varsity nylon Setup cost" in "info sheet" 'OCLVNxpt = OCLVNTCO + (OCLVNSUC / OCLVNGT) 'MsgBox "$" & OCLVNxpt & " " & "Per Transfer" 'xpt = wksin.Range("A" & adrqPCfir).Offset(xp - 1, wksws.Range("O" & artquan)).Value 'finding the printing cost for cell "O" in work sheet 'wksin.Range("I" & priceinfo) = xpt613 'transferring the printing cost to a cell in Info sheet cell "I" 'End If xadrqQGfir = xadrqQGfir + 1 xfstrwsaj = xfstrwsaj + 1 XadrqPRfir = XadrqPRfir + 1 Next End If 'If wksin.Range("G" & priceinfo) > 2 Then 'if the selection is not for printing then exit 'GoTo finish: 'End If '****************** '*Printing pricing* '****************** If Not wksws.Range("M" & fstrwsaj) = "" Then If Not (wksws.Range("A" & artquan) = "" Or wksws.Range("O" & artquan) = "E") Then xpt = wksin.Range("A" & adrqPCfir).Offset(xp - 1, wksws.Range("O" & artquan)).Value 'finding the printing cost for cell "O" in work sheet wksin.Range("I" & priceinfo) = xpt 'transferring the printing cost to a cell in Info sheet cell "I" End If If Not (wksws.Range("A" & artquan) = "" Or wksws.Range("P" & artquan) = "E") Then xpt = wksin.Range("A" & adrqPCfir).Offset(xp - 1, wksws.Range("P" & artquan)).Value 'finding the printing cost for cell "P" in work sheet wksin.Range("J" & priceinfo) = xpt 'transferring the printing cost to a cell in Info sheet cell "J" End If If Not (wksws.Range("A" & artquan) = "" Or wksws.Range("Q" & artquan) = "E") Then xpt = wksin.Range("A" & adrqPCfir).Offset(xp - 1, wksws.Range("Q" & artquan)).Value 'finding the printing cost for cell "Q" in work sheet wksin.Range("K" & priceinfo) = xpt 'transferring the printing cost to a cell in Info sheet cell "K" End If If Not (wksws.Range("A" & artquan) = "" Or wksws.Range("R" & artquan) = "E") Then xpt = wksin.Range("A" & adrqPCfir).Offset(xp - 1, wksws.Range("R" & artquan)).Value 'finding the printing cost for cell "R" in work sheet wksin.Range("L" & priceinfo) = xpt 'transferring the printing cost to a cell in Info sheet cell "L" End If '******************* '*613printing price* '******************* '***************** '*Embroidery Cost* '***************** If (wksws.Range("A" & artquan) = ChrW(8730) And wksws.Range("O" & artquan) = "E") Then xpt = wksws.Range("L" & artquan) 'finding the embroidery cost for cell "O" in work sheet If xpt = 0 Then MsgBox "no cost entered for embroidery" Else wksin.Range("I" & priceinfo) = xpt 'transferring the printing cost to a cell in Info sheet cell "I" End If End If If (wksws.Range("A" & artquan) = ChrW(8730) And wksws.Range("P" & artquan) = "E") Then xpt = wksws.Range("L" & artquan) 'finding the embroidery cost for cell "P" in work sheet If xpt = 0 Then MsgBox "no cost entered for embroidery" Else wksin.Range("J" & priceinfo) = xpt 'transferring the printing cost to a cell in Info sheet cell "J" End If End If If (wksws.Range("A" & artquan) = ChrW(8730) And wksws.Range("Q" & artquan) = "E") Then xpt = wksws.Range("L" & artquan) 'finding the embroidery cost for cell "Q" in work sheet If xpt = 0 Then MsgBox "no cost entered for embroidery" Else wksin.Range("K" & priceinfo) = xpt 'transferring the printing cost to a cell in Info sheet cell "K" End If End If If (wksws.Range("A" & artquan) = ChrW(8730) And wksws.Range("R" & artquan) = "E") Then xpt = wksws.Range("L" & artquan) 'finding the embroidery cost for cell "R" in work sheet If xpt = 0 Then MsgBox "no cost entered for embroidery" Else wksin.Range("L" & priceinfo) = xpt 'transferring the printing cost to a cell in Info sheet cell "L" End If End If '**************************************************************** '*in this section multiplying the cost of printing with profit %* '**************************************************************** profmat = WorksheetFunction.Match(wksin.Range("B" & xt).Value, wksin.Range("A" & adqPMfir & ":A" & adqPMlas), -1) xptl = wksin.Range("A" & adqPMfir).Offset(profmat - 1, 1).Value If Not (wksws.Range("A" & artquan) = "") Then wksin.Range("M" & priceinfo) = WorksheetFunction.Sum(wksin.Range("I" & priceinfo & ":L" & priceinfo)) wksin.Range("N" & priceinfo) = wksin.Range("A" & priceinfo).Value + wksin.Range("M" & priceinfo).Value wksin.Range("O" & priceinfo) = wksin.Range("N" & priceinfo).Value * xptl End If End If Next xt 'loop incriment '************************************************** '*In this section finding the cost for the screens* '************************************************** '***************************************************************************************************** xpt = 0 screen = 0 totallscreen = 0 totallscreenB = 0 For screen = adrqQGfir To adrqQGlas ssprmat = WorksheetFunction.Match(wksin.Range("F" & screen).Value, wksin.Range("A" & adrqSSPfir & ":A" & adrqSSPlas), -1) xpt = wksin.Range("A" & adrqSSPfir & ":A" & adrqSSPlas).Offset(ssprmat - 1, 1).Value 'finding the silk screen cost fro a table in column "A" & "B" If wksin.Range("F" & screen) = 0 Then wksin.Range("G" & screen) = "" Else wksin.Range("G" & screen) = xpt End If Next screen finish: 'Re-enable certain Excel Features while macro is ru Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True Application.ScreenUpdating = True End Sub Sub Calculate() Set wksws = Sheets("Work Sheet") 'assigning sheet6 to wksWS Set wksin = Sheets("Info Sheet") 'assigning sheet13 to wksIN If screenqunt = 1 Then ElseIf screenqunt = 2 Then screenqunt = screequnt + 7 ElseIf screenqunt > 2 Then screenqunt = screenqunt + 11 End If OCLVNTCO = wksin.Range("A" & adq61312ffir).Offset(0, screenqunt).Value 'cost "OCLVNTCO" "one color large varsity nylon transfer cost" in "info sheet" OCLVNSUC = wksin.Range("A" & adq61312ffir).Offset(0, screenqunt + 1).Value 'setup value "OCLVNSUC" "one color large varsity nylon Setup cost" in "info sheet" OCLVNFCO = OCLVNTCO + (OCLVNSUC / OCLVNGT) '"OCLVNFCO" "one color large varsity nylon Final cost" "OCLVNGT" "one color large varsity nylon garment total" MsgBox "$" & OCLVNxpt & " " & "Per Transfer" End Sub



    Vforum

    Sunday, November 17, 2019 10:43 PM
  • Your first line is

    Dim wksws, wkswin As Worksheet

    wskwin should be wksin, and it would be better to use

    Dim wksws As Worksheet, wksin As Worksheet

    to declare both variables as Worksheet.

    Although it will be annoying at first, I recommend having Option Explicit at the top of each module. See The importance of 'Option Explicit' for an explanation,


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 17, 2019 11:06 PM
  • You may also find A Dim understanding: declaring variables in VB/VBA and A matter of scope: visibility of code elements useful.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, November 17, 2019 11:11 PM
  • thank as usually and i will look at information you have send me.

    talk to you later.

    Kosta

     

    Vforum

    Sunday, November 17, 2019 11:24 PM
  • Hi Hans.

    I would like to do this and I tough figure it out, but I guess not.

    Once input baton pos up I have 5 options:

    1-    OK ill take any value and move on

    2-    Cancel will clear all cost values and exit the program “goto finish”

    3-    If the value Is “” then clear all cost values and exit the program “goto finish”

    4-    If value is “0” then clear all cost values and exit the program “goto finish”

    5-    If the value is >0 then proceed update the cell and go to the beginning to do it over goto cal613E

    I comment the lines that will give me a problem when I move the code to an empty macro for testing, but you will figure it out by now.

    Thank you.

    Kosta

    Sub test()

    cal613E:

               DefaultValue = "1"

               changeScre = InputBox("input (1) if you wish to proceed", "change it to one color", DefaultValue)

               If StrPtr(changeScre) = 0 Then '

               'wksin.Range("I" & adrqPRfir + 1 & ":O" & adrqPRlas) = ""

                GoTo finish

                ElseIf Value = vbNullString Then

                'wksin.Range("I" & adrqPRfir + 1 & ":O" & adrqPRlas) = ""

                GoTo finish

                End If

                'wksws.Cells(15, 10) = changeScre

                screenqunt = changeScre

                GoTo cal613E:

    finish:

     

    Continue:


    Vforum

    Thursday, November 21, 2019 7:54 PM
  • also is a way that i van change the color and the labels on the bottom in "input box: the color is so doll. 

    Vforum

    Thursday, November 21, 2019 8:08 PM
  • Perhaps something like this?

        …
        changeScre = Application.InputBox _
            (Prompt:="Input a number >0 if you wish to proceed", _
            Title:="Change it to one color", Default:=1, Type:=1)
        If changeScre = 0 Then
            wksin.Range("I" & adrqPRfir + 1 & ":O" & adrqPRlas) = ""
            Exit Sub
        End If
        wksws.Cells(15, 10) = changeScre
        screenqunt = changeScre
        ...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 21, 2019 8:40 PM
  • You cannot change the formatting of an inputbox; it is determined by Windows.

    You could create a userform with your own design, but that is a lot more work.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 21, 2019 8:41 PM
  • So, cancel and “0” works but if I put “” nothing then I get this error


    Vforum

    Friday, November 22, 2019 11:13 AM
  • Change

        changeScre = Application.InputBox _
            (Prompt:="Input a number >0 if you wish to proceed", _
            Title:="Change it to one color", Default:=1, Type:=1)

    to

        changeScre = Val(InputBox _
             (Prompt:="Input a number >0 if you wish to proceed", _
             Title:="Change it to one color", Default:=1))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 22, 2019 12:13 PM
  • that did it thank you.

    Kosta


    Vforum

    Friday, November 22, 2019 12:34 PM
  • What I am doing wrong this is so simple but is not working

    I am trying to but these three values to cell “B” 31,32, and 33 from a variant

    FstRwsAJ = 15,  LstRwsAJ = 25, and  LastRin = 498

    The comment out is what I would like to use but just for troubleshooting purposes I hard coded them and still did not work.

    this code is in the same module all the declarations done above 

                            'wksin.Range("C" & LrstRflsec + 2).Select
                            'wksin.Range("C31").Select
            If "FstRwsAJ" = wksin.Range("C31").Select Then
            'If "FstRwsAJ" = wksin.Range("C" & LrstRflsec + 2) Then
            wksin.Range("B" & LrstRflsec + 2) = FstRwsAJ
            wksin.Range("B" & LrstRflsec + 3) = LstRwsAJ
            wksin.Range("B" & LrstRflsec + 5) = LastRin
            End If


    Vforum

    Saturday, November 23, 2019 3:17 PM
  • The line

    If "FstRwsAJ" = wksin.Range("C31").Select Then

    should be

    If "FstRwsAJ" = wksin.Range("C31").Value Then


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 23, 2019 5:48 PM
  • I did that to see if it is selecting the correct cell.

    what is happening is going to "If" statement then is going to "end if" I know is looking the correct cell becouse if i do "select" dos go to the correct cell

    the "If" statement should say yes, it's match and then should execute the three lines immediately after the "If" statement but doesn’t goes to "end if"


    Vforum

    Saturday, November 23, 2019 8:21 PM
  • I'm sorry - I don't really understand what you're asking now.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 23, 2019 8:40 PM
  • this doesn't work form if goes to end if

    I did send a screen shot cell "C31" has "FstRwsAJ" why doesn't do the immediately three lines after the if before goes to end if  

            If "FstRwsAJ" = wksin.Range("C31").Value Then
            wksin.Range("B" & LrstRflsec + 2) = FstRwsAJ
            wksin.Range("B" & LrstRflsec + 3) = LstRwsAJ
            wksin.Range("B" & LrstRflsec + 5) = LastRin
            End If


    Vforum

    Saturday, November 23, 2019 8:46 PM
  • Hans id find the problem was a space after  "FstRwsAJ" and that was the problem.

    thank you any way.

    Kosta


    Vforum

    Saturday, November 23, 2019 8:59 PM
  • Aha - that's what I was going to ask next...

    Glad that you found it.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 23, 2019 9:17 PM
  • yes it's invisible can drive you crazy as did to me.

    thank you as usual.

    Kosta


    Vforum

    Saturday, November 23, 2019 9:20 PM
  • what is the correct syntax for this statement

    let me know please

    Kosta 

    alseif Not wksin.Range("B" & xadrqQGfir) >= 8 then


    Vforum

    Monday, November 25, 2019 2:27 AM
  • alseif should be ElseIf

    You could change the line to

    ElseIf wksin.Range("B" & xadrqQGfir) < 8 Then


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Monday, November 25, 2019 8:07 AM
  • thank you.

    Kosta


    Vforum

    Monday, November 25, 2019 4:11 PM
  • Hi Hans.

    I have this in a test excel as a stand along code and works fine.

    I copy and paste it to my main program, and I get an error

    Run-time error 13 type mismatch

    In the line “If profitinfo = 0 Or profitinfo = "" Then”

     

    profitinfo = Val(InputBox _

                                (Prompt:="Default value is 15% enter new value or click ok or hit the enter key", _

                                Title:="Enter Profit Margin", Default:=15)) 'Input Box asking to enter the profit margin default is 15%

                                If profitinfo = 0 Or profitinfo = "" Then 'If the return answer from input box is "cancel" or "0" or "" then clear all cost and exit the program

                                wksin.Range("I" & adrqPRfir + 1 & ":O" & adrqPRlas) = "" 'Clearing all costs from range "I" to "O" in "info sheet"

                                wksws.Range("K" & FstRwsAJ & ":K" & LstRwsAJ) = "" 'Clearing all stich counts from column "K" in "work Sheet

                                MsgBox "No Profit margin entered Clearing all cost values and exiting the program", vbExclamation

                                GoTo finish:

                                Exit Sub

                                Else: wksin.Range("E" & adqPMfir) = "" 'Clearing the profit margin value"

                                If profitinfo < 15 Then profitinfo = 15

                                wksin.Range("E" & adqPMfir) = profitinfo / 100

                                End If


    Vforum

    Monday, November 25, 2019 4:24 PM
  • A numeric variable cannot be equal to the string value "".

    Testing for "" isn't necessary since Val converts the result of InputBox to a number. If the user enters a text value or leaves the InputBox blank, Val will return 0. So you can change the line

    If profitinfo = 0 Or profitinfo = "" Then

    to

    If profitinfo = 0 Then


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 25, 2019 6:30 PM
  • I understand I have this code line working in about 6 more spots without a problem and this works in a blank workbook.

    I just don't understand this, every time I am doing something that I know works a new problem comes for what i though  is a working code

    Any way

    it worked

    Thank you for your patient.


    Kosta 

     

    Vforum

    Monday, November 25, 2019 6:45 PM
  • It depends on whether you have declared the variable profitinfo in a specific macro, and if so, how.

    If you haven't declared profitinfo, or declared it as String, comparing it to "" will be allowed.

    But if you have declared profitinfo as a numeric variable, for example as Long or Double, it cannot be compared to a string value such as "".


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 25, 2019 7:22 PM
  • The profitinfo I did declare in the beginning of the program as Long

    And the changeScre has no declaration I guess I forgot and that is why is working without any problem

    But as I found out I do not need to check for “” changeScre = "" or profitinfo = “”

    I am going to remove those extra codes and I will declare changeScre as Long also

    I understand now.

    Slowly learning.

    Thank you,

    Kosta

    Vforum

    Monday, November 25, 2019 7:45 PM
  • Good Morning Hans

    How can I do this the ("E" & adrqPRfir) doesn’t changes I can even hard coded as “Style#”

    the second (“E" & adrqPRfirA) gets incremented

    I believe the first two sections of the DGET code below is working I think the problem is the last section

    wksin.Range("E" & adrqPRfir & "E" & adrqPRfirA)

     

    Wat I am trying to do is to combine two different cells so DGET knows what to look in the data base (the column name is “Style#” in the data base and the style number is entered from the user) each row may have deferent style number

    I am using DGET becouse I believe this is the fastest way to get the date from a long data base if you think a faster way let me know please. I am extracting 7 deferent informations from the same row in the data base once I match the style number.

    The date base where I am looking is about 5000 lines and it’s growing

    I hope I explained myself.

    Thank you

    Kosta

    varDGet = WorksheetFunction.DGet(wksPPAI.Range("A1:H" & lastrowPPai), wksin.Range("A" & adrqPRfir), wksin.Range("E" & adrqPRfir & "E" & adrqPRfirA))

     


    Vforum

    Wednesday, November 27, 2019 11:29 AM
  • Change

    wksin.Range("E" & adrqPRfir & "E" & adrqPRfirA)

    to

    wksin.Range("E" & adrqPRfir & ":E" & adrqPRfirA)

    with a : before the second E.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 27, 2019 1:04 PM
  • Thank you

    This what is happening, and I did try your suggestion before

    The first two passes are ok becouse the style number is the same.

    When gets to the third pass it’s a deferent style number and doesn’t like it I have included two snapshots the first snapshot is what is happening, and the second snapshot is what I want to happen

    At list that is what I think should happen so the DGET can find the price for that style of garment

    Kosta

    varDGet = WorksheetFunction.DGet(wksPPAI.Range("A1:H" & lastrowPPai), wksin.Range("A" & adrqPRfir), wksin.Range("E" & adrqPRfir & ":E" & adrqPRfirA))

            wksin.Range("A" & adrqPRfirA) = varDGet



    Vforum

    Wednesday, November 27, 2019 2:37 PM
  • The second argument of DGet should be the field name. Since your database range (the first argument) starts in A1, I assume that the field names are in row 1. So the second argument should probably be wksin.Range("A1")

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 27, 2019 2:56 PM
  • Sorry i am not clear.

    this is what i want to happen but is not happening i am getting error 

    arror #1004 Run-time 

    Unable to get the DGET property of the worksheetFunction Class

    i am totally confused.

    Kosta

           varDGet = WorksheetFunction.DGet(wksPPAI.Range("A1:H2500"), "Price", wksin.Range("E40,E41"))
            varDGet = WorksheetFunction.DGet(wksPPAI.Range("A1:H2500"), "Price", wksin.Range("E40,E42"))
            varDGet = WorksheetFunction.DGet(wksPPAI.Range("A1:H2500"), "Price", wksin.Range("E40,E43"))


    Vforum

    Wednesday, November 27, 2019 5:12 PM
  • Hans i thing i know why is not working but I cannot figure it out

    I did a test try to select each component separately

    And I am getting this error Run -time 1004 select method of range failed

    I did some more testing and determined that is not selecting the worksheet.

    If I am on the work sheet that the code is looking doesn’t give me an error so before I go to next select a change the worksheet to the one the code will look and works without an error.

    Any suggestions

    Kosta

                    wksin.Range("E" & adrqPRfir, "E" & adrqPRfirA).Select

                   wksin.Range("E40, E41").Select

                   wksin.Range("E40, E42").Select

                   wksin.Range("E40, E43").Select

                   wksPPAI.Range("A1", "H2307"). Select

                   wksin.Range("A" & adrqPRfir).Select

    and this the beginning of the program 

    Sub PPAI()
        Dim wksws As Worksheets, wksin As Worksheets, wksPPAI As Worksheets
        Dim r As Long
        Dim AJTO As Long
        Dim AJBO As Long
        'Dim adrqPRfir As String
        Dim adrqPRlas As Long
        Dim adrqQGfir As Long
        Dim adrqQGlas As Long
        Dim adrqPCfir As Long
        Dim adrqPClas As Long
        Dim adrqSSPfrs As Long
        Dim adrqSSPlas As Long
        Dim adqPMfirs As Long
        Dim adqPMlas As Long
        Dim ssprmat As Long
        Dim prcmat As Long
        Dim profmat As Long
        Dim xp As Long
        Dim FrstRflsec As Long
        Dim LrstRflsec As Long
        Dim lastrowPPai As Long
        Dim LstRwsAJ As Long
        Dim LastRin As Long
        Dim Frsta As Long
        Dim FrstB As Long
        Dim Frst As Long
        Dim Lrst As Long
        Dim varDGet As Variant
       
       
       
        Set wksin = Sheets("info Sheet") 'assigning sheet13 to wksin
        Set wksws = Sheets("Work Sheet") 'assigning sheet6 to wksws
        Set wksPPAI = Sheets("PPAI") 'assigning sheet3 to wksppai


    Vforum

    Wednesday, November 27, 2019 7:01 PM
  • Which of the lines with Select causes the error?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 27, 2019 7:51 PM
  • Depends if i have info sheet open then that works fine but the wksppai line  gives me the error if i have the ppai open then wksin line gives me an error.

    I don't understand this.

    Kosta

     

    Vforum

    Wednesday, November 27, 2019 9:34 PM
  • You can only select cells in the active sheet. So for example if "Info Sheet" is the active sheet, you cannot select cells in the PPAI sheet.

    It is often not really necessary to select cells in VBA code, but if you need to do that, you should first select the worksheet, then select the cells.

    For example:

                   wksin.Select
                   wksin.Range("A" & adrqPRfir).Select


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 27, 2019 10:09 PM
  • that worked but i did not have to do this in my other modulas, al this is done in the same workbook.

    Any way.

    now what do i have to do for this line to work.

    Kosta 

     varDGet = WorksheetFunction.DGet(wksPPAI.Range("A1:H2500"), wksin.Range("A" & adrqPRfir), wksin.Range("E40,E41"))

    Vforum

    Wednesday, November 27, 2019 10:18 PM
  • Change wksin.Range("E40,E41") to wksin.Range("E40:E41")

    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Wednesday, November 27, 2019 10:45 PM
  • Good morning Hans,

    First, I do not know where you are but if you are in the USA happy Thanksgiving.

    And now to my problem.

    This whole thing is about how DGET works.

    DGET(data base, Field, criteria)

    Data base & field is no problem

    The problem is in the criteria and the question is.

    Is the criteria information must be two consecutive rows like A1 and A2 or can they be A1 and A3 not A1 trough A3?

    If must then there is my problem

    And I don’t think the DGET works in VBA if the information is not in the same sheet apparently.

    Becouse my data base in sheet 3 and my field and criteria are in sheet 13.

    This is where I am right now, depends you answer the I will do something accordingly.

    As usual thank you,

    Kosta


    Vforum

    Thursday, November 28, 2019 8:18 PM
  • I live in The Netherlands, but thank you!

    The Criteria argument of DGet MUST be a contiguous range, it cannot consist of disjunct cells. The first row of this range must contain field name(s), and the row(s) below should contain the condition(s).

    In your example, the Criteria range wksin.Range("E40:E41") should have a valid field name from the Database range in cell E40, and the value or expression you want to match on in cell E41.

    The Criteria range CAN be on another sheet than the Database range. So it should not be a problem that the Database is on sheet 3 and the Field and the Criteria are on Sheet 13.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 28, 2019 8:59 PM
  • I figure that from your logo.

    I had a pen pal from Amsterdam when I was leaving in Greece long time ego.

    Back to this DGET I had big problem when I was trying to call deferent worksheets a was getting errors.

    Any way i did something different I did not use the DGET and is working fine.

    I am very sure I will be talking to you soon.

    As usual thank you.

    Kosta 


    Vforum

    Thursday, November 28, 2019 10:49 PM
  • Hi Hans.

    Question the “rng” information I assume will be the row is the criteria that I am searching.   

    I have a date base that has three columns (A, B, and C) and I want to find the row that matches the search criteria in Column “C”

    Is this correct? I am searching in column “C” so if the criteria match then I should get the row number.

    Becouse I am not getting this it’s seams like is looking row “A” not “C”

    lastrwoCC = 1719 that is the last row in the database

    Findrow = 802 these is value I am looking in column “C”

    Let me know please

    Set rng = wksCC.Range("C2:C" & lastrwoCC).Find(what:=Findrow, SearchDirection:=xlPrevious, SearchOrder:=xlByRows, lookat:=xlWhole, LookIn:=xlValues)

    ROW      A             B             C

    744         743         1724       802 I should get this row 744

    745         744         1725       803

    746         745         1726       804

    747         746         1727       805

    748         747         1728       806

    749         748         1729       807

     

    ROW      A             B             C

    802         801         1776       855 But I think I am getting this becouse I am getting row 802

    803         802         1777       856

    803         803         1778       857

    805         804         1779       858

    806         805         1780       859


    Vforum

    Saturday, November 30, 2019 3:20 PM
  • rng is the range; since you are searching for 802, the value of rng is 802.

    If you want to know the row number of the cell, you have to look at rng.Row:

        Dim RowNum As Long
        Set rng = wksCC.Range("C2:C" & lastrwoCC).Find(What:=Findrow, SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookAt:=xlWhole, LookIn:=xlValues)
        If rng Is Nothing Then
            MsgBox "Value not found!"
        Else
            RowNum = rng.Row
        End If


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 30, 2019 3:44 PM
  • Thank you, Hans that did work,

    I have another Question

    Is this going to search the entire data base “frs”

    To find anything that match “C” and returned the row # back

    For example, if I but company id# 1230 I wand to return to me the row that has #1230 of course I am using a loop, so I am going to find all the rows that have 1230

    Another example will be if I enter characters like the beginning of a company’s name say “corde”

    Let me know if this will work.

    Thank you,

    Kosta

     

    Set frs = wksCM.Range("A2").CurrentRegion

    Set rs = frs.Find(what:=wksCP.Range("C2"), SearchDirection:=xlPrevious, SearchOrder:=xlByRows, lookat:=xlWhole, LookIn:=xlValues)


    Vforum

    Sunday, December 1, 2019 4:16 PM
  • To find all instances, use code like this

        Dim sAddress As String
        Set frs = wksCM.Range("A2").CurrentRegion
        Set rs = frs.Find(What:=wksCP.Range("C2"), SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows, LookAt:=xlWhole, LookIn:=xlValues)
        If Not rs Is Nothing Then
            sAddress = rs.Address
            Do
                ' Your code here
                ...
                ...
                Set rs = frs.FindNext(After:=rs)
                If rs Is Nothing Then Exit Do
            Loop Until rs.Address = sAddress
        End If

    If wksCP.Range("C2") contains the first letters of the item that you want to search for, change

    What:=wksCP.Range("C2")

    to

    What:=wksCP.Range("C2") & "*"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 1, 2019 4:56 PM
  • Got it thank you, Kosta

    Vforum

    Sunday, December 1, 2019 10:25 PM
  • Good evening Hans

    I did not know how attached the pictures so i am sending you a link.

    https://1drv.ms/w/s!ApL41UVs1W9k1yAMFyxDqbd4cj0C?e=x6Rv8f


    Vforum

    Monday, December 2, 2019 5:10 PM
  • What you describe in the Word document is not really how Excel works. Wouldn't it be possible to keep the names in separate cells?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 2, 2019 7:14 PM
  • I did found it out

    please check this suggestion 

    https://1drv.ms/w/s!ApL41UVs1W9k1yAMFyxDqbd4cj0C?e=c2KEjk


    Vforum

    Monday, December 2, 2019 7:39 PM
  • See https://www.contextures.com/xlDataVal01.html

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 2, 2019 8:16 PM
  • Hans check this please.

    Kosta

    https://1drv.ms/w/s!ApL41UVs1W9k1yVErJhuYkPMTerQ?e=xyjjOo


    Vforum

    Wednesday, December 4, 2019 1:00 AM
  • In the code that you posted, Farmula1 should be Formula1.

    It will add a data validation dropdown to the first cell in the second column of the table named Database. The dropdown list will be populated from the range named PropertyList.

    I assume that you want to add validation to the entire column instead of just to its first cell. If that is correct, change

        Set dvProperty = tbl.DataBodyRange(1, 2)

    to

        Set dvProperty = tbl.ListColumns(2).DataBodyRange 

    Here is the complete code:

    Sub datavalidation()
        Dim ws As Worksheet
        Dim tbl As ListObject
        Dim dvProperty As Range
        Set ws = ActiveSheet
        Set tbl = ws.ListObjects("Database")
        Set dvProperty = tbl.ListColumns(2).DataBodyRange
        With dvProperty.Validation
            .Delete
            .Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Formula1:="=PropertyList"
            .ErrorTitle = "Property"
            .ErrorMessage = "Please select an item from the dropdown list!"
        End With
    End Sub

    You can see what the code does in the sample workbook at https://www.dropbox.com/s/e1yxoqxgx4bojk5/DV4Kosta.xlsm?dl=1.

    The second column of the table currently does not have data validation, but when you click the command button, you'll see a dropdown arrow when you select a cell in this column.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 4, 2019 11:38 AM
  • thank you

    Kosta


    Vforum

    Wednesday, December 4, 2019 12:00 PM
  • Hi! Try to use ZetExcel.com. It is very helpful.
    Monday, December 9, 2019 9:44 AM
  • thank you Steven, I just send them a message i will let you know what they response was.

    Kosta

     

    Vforum

    Monday, December 9, 2019 7:45 PM
  • Hi Hans, good afternoon

    I have attached a file that has two sections

    The fist section is where a create a validation and dropdown menu

    The second section (starts at Delete: line 53) is where I delete all the validation names

    I did copy this from someplace else but works.

    Two things about this deletion:

    First asking to “Do you want to skip over Print Areas?” I do not understand this question.

    And second I only want to delete any names that has the word “Validation”

    This seams that deletes all the name in the workbook I do not want this to happen. 

    Let me know please.

    Kosta

    https://1drv.ms/x/s!ApL41UVs1W9k2zET4Cwg0Pk9Urwk?e=V60qu7


    Vforum

    Tuesday, December 10, 2019 2:31 PM
  • Second question is once a pick a name (in the sample file I have numbers but in reality are names of contact personness)from the dropdown menu haw can I run another macro when I click that name.


    Vforum

    Tuesday, December 10, 2019 2:35 PM
  • You can specify the area of a worksheet that will be printed. Anything outside that area will be left out of a printout.

    When you do so, Excel will create a defined name Print_Area:

    When you delete defined names, you may not want to include Print_Area, because that would remove the print area setting. Hence the question.

    To delete only names containing "validation", you don't need that question - it is superfluous.

        'Deleting names that contain "validation"
        For Each nm In ActiveWorkbook.Names
            If InStr(1, nm.Name, "validation", vbTextCompare) = 0 Then
                'Error Handler in case Delete Function Errors out
                On Error GoTo Skip
                'Delete Named Range
                nm.Delete
                DeleteCount = DeleteCount + 1
            End If
    Skip:
            'Reset Error Handler
            On Error GoTo 0
        Next nm


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 10, 2019 3:13 PM
  • You can do that in the Worksheet_Change event procedure in the module of the ControlP sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("N1"), Target) Is Nothing Then
            Application.EnableEvents = False
            ' Your code goes here
            ...
            Application.EnableEvents = True
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 10, 2019 3:17 PM
  • https://1drv.ms/w/s!ApL41UVs1W9k20_x3EfXXRgsJGGY?e=NPy1mH

    Vforum

    Tuesday, December 10, 2019 3:52 PM
  • Sorry, change

            If InStr(1, nm.Name, "validation", vbTextCompare) = 0 Then

    to

            If InStr(1, nm.Name, "validation", vbTextCompare) > 0 Then


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 10, 2019 4:08 PM
  • this did the job thank you,

    Kosta


    Vforum

    Tuesday, December 10, 2019 7:59 PM
  • Hans, the range "N1" can be "N2" or "N3.

    I am searching in "company Master Data" a company that has 802, so the results maybe couple companies that has the 802.

    then next to each company puts a dropdown manu with the contact name some time is one contact per company and some time is more..

    is this going to work if i pick a name that is on the third row "N3"?.

    let me know please.

    Kosta


    Vforum

    Tuesday, December 10, 2019 8:07 PM
  • I'm afraid I cannot visualize this.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 10, 2019 8:40 PM
  • Good Morning Hans.

    As you see in the file that I have send you next to the edit button from each company I have a dropdown menu I want to be able to click to any name and get the persons information.

    In this example I have two different companies sometimes will be more than that.

    You think something like the code below will work? The problem with this code is that is very specific.

    Every time I am doing a search, I may gate different name. if you look down to call column D59 I do capture the ID# number of the person I can use that to get the information that I need from the data base.

    But I cannot figure out how to implement it with the code below or if the code below is the right one.

    Let me know please.

    Kosta  

     

     

     

     

    Private Sub Worksheet_Change(ByVal Target As Range)

        If Not Intersect(Target, Range("E1")) Is Nothing Then

            Select Case Range("E1")

                Case "Insert Blank rows": Macro1

                Case "Hide All Sheets": Macro2

                Case "Convert to Date": Macro3

            End Select

        End If

    https://1drv.ms/x/s!ApL41UVs1W9k2zET4Cwg0Pk9Urwk?e=3hV6pO


    Vforum


    • Edited by Kosta si Wednesday, December 11, 2019 11:23 AM
    Wednesday, December 11, 2019 11:22 AM
  • I don't understand why you have validation dropdowns with only one item...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 11, 2019 12:27 PM
  • Depends on the search and depends on each company some companies may have more then one contact pesrson.

    When the search for the company happens I do not know if one ore more then one contact name(s) will popup.

    I put them in a temporary place and then I create a dropdown menu for the user to select. 

     

    Vforum

    Wednesday, December 11, 2019 12:36 PM
  • I would use dependent validation drop down lists, where the list of contact persons depends on the company entered/selected in column N.

    See https://www.contextures.com/xlDataVal02.html


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 11, 2019 2:58 PM
  • Hans, I hope you are not board with me.

    I pause my project and start a new one becouse this one king has a deadline.

    As you know I am retired I have my business, but I do it only for costumers that I had for many years.

    I am also a volunteer to a big youth soccer league for youth(kids).

    I am the chairman of the games committee means my group administrate over 1600 teams playing each other every week for 9 weeks fall and spring seasons

    In the end of the season each team reregisters the team for the next season. My team also reasponsiball to create a division for about 10 teams according to there ability age group and gender, and we come up with groups (divisions) that they will play each other in the upcoming season,

    I have a program that does that I am not happy (does the job), but I will get to it later for some improvements.

    Right now I have this task I have two files one of it Is ” Spring 2019 Division Standings” in this file I have the results of the spring competition and the teams that participated, and then I have a file “Register Teams” that have the new registered teams.

    My task is to find witch teams coming back copy the information that is in “"register teams fall 2019”  

    To file “register and unregister teams fall 2019” sheet 1 and what is left over to sheet 2

    As you notice the date is of last years this year’s files will be fall 2019 and register teams spring 2020 that means every season the file name is changing.

    For test purposes I am using last years files as the new data is not in my hand yet.

    I start creating a program I am sending all three files to you.

    The one I am working is the file “register and unregister teams fall 2019” macro 2 I already stuck the set commands don’t want to work if the corresponding files are not open also, I don’t know if the syntax is correct, and when I am trying to find the last row of each file that doesn’t work either.

     

    Let me know

    Kosta

    https://1drv.ms/x/s!ApL41UVs1W9k21Q0RHAzkpdv_M8J?e=FIXzKE

    https://1drv.ms/x/s!ApL41UVs1W9k21YYN4-eOBMBPOzD?e=6Q5QOY

    https://1drv.ms/x/s!ApL41UVs1W9k21KOLbznYLZ6bHkR?e=EKvzs7


    Vforum

    Wednesday, December 11, 2019 5:41 PM
  • The macro is unfinished of course, but here is a corrected version:

    Sub extract()
        Dim wbSS As Workbook
        Dim wbRT As Workbook
        Dim wksRT As Worksheet
        Dim wksURT As Worksheet
        Dim strPath As String
        Dim lastrowRT As Long
        Dim lastrowSS As Long
        strPath = ThisWorkbook.Path & "\"
        On Error Resume Next
        Set wbSS = Workbooks("Spring 2019 Division Standings.xlsx")
        If wbSS Is Nothing Then
            Set wbSS = Workbooks.Open(strPath & "Spring 2019 Division Standings.xlsx")
        End If
        Set wbRT = Workbooks("register teams fall 2019.xlsx")
        If wbRT Is Nothing Then
            Set wbRT = Workbooks.Open(strPath & "register teams fall 2019.xlsx")
        End If
        On Error GoTo 0
        lastrowSS = wbSS.Worksheets("sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
        lastrowRT = wbRT.Worksheets("sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
        Set wksRT = ThisWorkbook.Worksheets("Register Teams")
        Set wksURT = ThisWorkbook.Worksheets("Unregister Teams")
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 11, 2019 8:20 PM
  • Thank you

    I need to select the file for “wbSS” and the file for “wbRT” this cannot be hard coded becouse they change every season.

    I need to able to select these files, I can keep the same name, but the date will be deferent is that doable.

    Let me know please.

    Kosta

    Vforum

    Wednesday, December 11, 2019 10:12 PM
  • Here is a new version:

    Sub extract()
        Dim wbSS As Workbook
        Dim wbRT As Workbook
        Dim wksRT As Worksheet
        Dim wksURT As Worksheet
        Dim strPath As String
        Dim strFile As String
        Dim lastrowRT As Long
        Dim lastrowSS As Long
        strPath = ThisWorkbook.Path & "\"
        With Application.FileDialog(1)
            .Title = "Select the Division Standings workbook"
            .InitialFileName = strPath & "*.xlsx"
            If .Show Then
                Set wbSS = Workbooks.Open(.SelectedItems(1))
            Else
                Beep
                Exit Sub
            End If
            .Title = "Select the register teams workbook"
            .InitialFileName = strPath & "*.xlsx"
            If .Show Then
                Set wbRT = Workbooks.Open(.SelectedItems(1))
            Else
                Beep
                Exit Sub
            End If
        End With
        lastrowSS = wbSS.Worksheets("sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
        lastrowRT = wbRT.Worksheets("sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
        Set wksRT = ThisWorkbook.Worksheets("Register Teams")
        Set wksURT = ThisWorkbook.Worksheets("Unregister Teams")
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 11, 2019 10:24 PM
  • Good morning Hans.

    Is any short way to get an exact match for one row of on file to other row of another file with some columns not to be looked up in my instant I will like to look each row in division standings (only columns B, D, E, F, and G) against the same columns in register teams and if it’s a match do something if not do something else

    thanks

    Kosta


    Vforum

    Thursday, December 12, 2019 10:37 AM
  • Hans in file Register Teams Column "D" the formatting doesn't match the formatting in column "D" in Division standings

    Row 63 in division standings should match row 51 in register teams but in one I get 565 and in the other one I get "565" I also see that in Register team column "D" has a leading (0) before the number I try to fix it but is not working.

    let me know please.

    Kosta 

     

    Vforum

    Thursday, December 12, 2019 12:31 PM
  • The values in column D of the Division Standings workbook are numbers, while those in the Register Teams workbook are text values.

    If you wish, you can convert those text values to numbers:

    • Select column D of the Register Teams workbook.
    • Activate the Visual Basic Editor (Alt+F11).
    • Activate the Immediate window (Ctrl+G).
    • Type or copy paste the line

    Selection.Value = Selection.Value

    • Press Enter.
    • Switch back to Excel.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 12, 2019 12:54 PM
  • You could create a helper column, with the following formula in row 2, then filled down:

    =B2&D2&E2&F2&G2

    If you do this in both workbooks, you can compare the helper columns.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 12, 2019 12:56 PM
  • why i cannot select Column D go to format cell and select number bring the decimal point to none.

    I am trying to do it but is not working I should be abel to change it to what ever a want to.

    Kosta


    Vforum

    Thursday, December 12, 2019 1:11 PM
  • I got it thank you,

    why the format cell did not work?

    and after i did what you told me to do now is working

    Kosta


    Vforum

    Thursday, December 12, 2019 1:18 PM
  • Changing the number format has no effect on text values, only on "real" numbers.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 12, 2019 2:12 PM
  • Hans,

    I did finish this face of the project after I fix the problem with column "D".

    Couple things do I have to open the files "division Standings" and register team" files to do this?

    If the files are opened can I closed after te task is finished?

    And last is this the most officiant way doing this? or is a faster way.

    As usually thank you for your help.

    Kosta

    https://1drv.ms/x/s!ApL41UVs1W9k21Q0RHAzkpdv_M8J?e=002Uin


    Vforum

    Thursday, December 12, 2019 2:25 PM
  • To manipulate data from other workbooks, the easiest way to do so is by opening them in Excel.

    You can close the two workbooks at the end of the macro:

        wbSS.Close SaveChanges:=False
        wbRT.Close SaveChanges:=False

    This will close them without saving changes (if any). If you would like to save them, change SaveChanges:=False to SaveChanges:=True

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 12, 2019 4:17 PM
  • Hi Hans,

    I run into a problem not my generally human problem

    Somehow club names and team names are not identical when they entered to the data base some time the abbreviate them.

    So, I was thinking can I have an input window asking what I want to search

    Gender, team year, club id, club name, team Id, team name I can pick any or all of them and a search will happen with the selected criteria.

    Then I have to figure out how to go about it and make it happen

    If you help me with input window and the results, I can figure out the rest of them

    Thank you

    Kosta

    Vforum

    Thursday, December 12, 2019 9:22 PM
  • I'm afraid that is a bit much. I'd simply use Excel's Find dialog.

    If you select one or more columns before pressing Ctrl+F, Excel will search in those columns only.

    You can specify whether you want to match entire cell contents or not.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thursday, December 12, 2019 9:43 PM
  • God Morning Hans

    I need to find if the last digit of text “G001” or M001”

    Each row and then change the year from 2000 to 2001

    Remember this changes every season so I need to have way to ask what I am looking and to what I am changing it to this year will be “G002” or “M002”

    Let me know please.

    Kosta  

     F             2000       1              245         Port Washington              G100

    F              2000       1              257         Lindenhurst        G001

    F              2000       1              93           Cambria Heights               G000

    F              2000       1              305         Plainedge            G300

    F              2000       1              45           Plainview Old Bethpage G000

    F              2000       Champion           51           Glen Cove           G001

    F              2000       Champion           311         Smithtown          G101

    F              2000       Champion           676         Elwood G001

    M            2000       2              52           Garden City        B301

    M            2000       2              313         South Huntington            B000

    M            2000       2              288         Lynbrook/E. Rockaway  B000

    M            2000       2              360         Manhasset         B000

    M            2000       2              272         NPT/CH B300

    M            2000       2              55           Oceanside           B100

    M            2000       2              52           Garden City        B100

    M            2000       Champion           316         Valley Stream    B001

    M            2000       Champion           272         NPT/CH B000


    Vforum

    Friday, December 13, 2019 1:34 PM
  • Just press Ctrl+H to display the Replace dialog...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, December 13, 2019 2:04 PM
  • unfortunately I know how do this but I have volunteers that don't know to do this actually don't know anything about excel at all,

    I have to password protect anything I put out for them to use it so they don't destroy the program and they still find a way to do it.

    I need to have something that works and helps them, if doesn't work they stop using it and all the effort becomes waisted

    Kosta 


    Vforum

    Friday, December 13, 2019 2:14 PM
  • Please explain in as much detail as you can what the user should enter, and what should happen based on the input.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, December 13, 2019 2:51 PM
  • let me try to see if i can if not i will ask you for your help.

    this way a learn.

    Kosta


    Vforum

    Friday, December 13, 2019 4:27 PM
  • Hans,

    I have created a input form but every time I open the form is in the wrong place.

    I have two screens in one of it is the VBA editor the right screen and the left screen has the worksheet that i want the form to apear.

    I am trying to find how to do it in the internet but I cannot find it can you help please.

    This is how I am opening the input form

    Selectwts.Show

    And i want it in this sheet 

    Set wksRT = ThisWorkbook.Worksheets("Register Teams").

    Thank you,

    Kosta 


    Vforum

    Saturday, December 14, 2019 7:29 PM
  • Does this work?

    Private Sub UserForm_Initialize()
        Me.Top = Application.Top + Application.InchesToPoints(3)
        Me.Left = Application.Left + Application.InchesToPoints(1)
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, December 14, 2019 7:51 PM
  • yes Thank you<

    Kosta


    Vforum

    Saturday, December 14, 2019 8:57 PM
  • Hans this was working until yesterday now is not working

    I have a copy in two different modals both was working yesterday

    I did make changes in one module but the other one I did not touch and now both not working.

    The error that I am getting is Run-time error 9 Subscript out of range

    This the line that is not working both files are open

    lastrowDS = wbSS.Worksheets("sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row 'Finding the last row that has a data in workbook "Division Standings"

    the other one is working becouse is the active sheet always.

    Also, before the sorting starts the active sheet should be the “register and unregister team” sheet Register teams

    Vforum

    Saturday, December 14, 2019 10:18 PM
  • Is the worksheet in wbSS (the Division Standing workbook) still called Sheet1 ?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, December 14, 2019 10:25 PM
  • no I fix it sorry.

    thank you,

    Kosta


    Vforum

    Saturday, December 14, 2019 11:59 PM
  • Hans,

    why this is not working

    Watch :   : thisFileName : "register and unregister teams fall 2019.xlsm" : String : search.findreregisterdteams

    Set wbRAUT = Workbooks(thisFileName).Worksheets("Register Teams")

    and then after this works this is what i want to do

    wbRAUT.range("A1").select

    let me know please

    thank you,

    Kosta


    Vforum

    Sunday, December 15, 2019 9:52 AM
  • Before you can select a range, you must make sure that the workbook is the active workbook and that the worksheet is the active sheet.

    So try

    Workbooks(thisFileName).Activate
    wbRAUT.Select
    wbRAUT.Range("A1").Select


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 15, 2019 12:03 PM
  • I am getting error Run time error 91

    Object variable or with block variable not set

    I have the variable set as workbook

    Dim wbRAUT As Workbook


    Vforum

    Sunday, December 15, 2019 2:30 PM
  • Your line

    Set wbRAUT = Workbooks(thisFileName).Worksheets("Register Teams")

    means that wbRAUT is a Worksheet object, not a Workbook object, so the declaration should be

    Dim wbRAUT As Worksheet


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 15, 2019 4:25 PM
  • no one of it is

    Set wksRT = ThisWorkbook.Worksheets("Register Teams")

    and this is set as a worksheet

    the other one isWorkbooks(thisFileName).Activate

    in the watches window show as file name with the extension so has to be a workbook   

    Watch :   : thisFileName : "register and unregister teams fall 2019.xlsm" : String :

    any way i am getting the same error after I change it to worksheet

    I am attaching the file if you wish to run it you must run it from "register and unregister teams" and just hit the blue button on top.

    do not allow it to run all the way will take about 5 minutes to do all that searching put a brake in line about 101

    the error happens in line 92.

    also dos create a temp file when i finish doing what i am doin before i close the program i will delete the temp file

    for nor you must deleted manually if you wish to rerun the program

    Let me know

    Thank you

    Kostahttps://1drv.ms/x/s!ApL41UVs1W9k21Q0RHAzkpdv_M8J?e=iDaKGm


    Vforum


    • Edited by Kosta si Sunday, December 15, 2019 6:35 PM
    Sunday, December 15, 2019 6:34 PM
  • In the workbook in your link, the line

    'Set wbRAUT = Workbooks(thisFileName).Sheets("Register Teams")

    is a comment because of the ' at the beginning, so wbRAUT is not set to anything.

    P.S. You don't have Option Explicit at the top of your modules, and you don't declare most of your variables. This makes it harder to analyze and debug the code.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 15, 2019 8:44 PM
  • Hans I don’t know if option explicate is a must if the code works.

    I understand disciplines you to have everything declared I will go through it and try to do it.

    Yes, I comment that line becouse I though you want it me to use these lines only

    Workbooks(thisFileName).Activate

        wbRAUT.Select

        wbRAUT.Range("A1").Select

    I did not realize that these lines are edition to my line.

    I remove the (‘) comment and is working

    This project supposed to be a simple a match data and sort project and now is turning to be more complicated.

    • First, I have to match the division standings with the new register teams and retain only the matched team from the division standings becouse they have the match record that we need to use when we are creating the competition groups(division)-that is done
    • Second have to check from register teams what was not a match and bring them in to the register list as a new team with no record-that is done
    • Third when we are getting ready to create the groups, we need to check the winning records then we group teams accordingly and the once that don’t have record, we depend on their application papers letting us know where they want to be placed. In the the fall season, that is ok becouse all those teams are starting from the scratch becouse all teams move up one year we still use the spring record, but we rethink the groups as a new team. In the spring is deferent most of teams don’t play in the fall specially the older kids becouse the play school ball. All the kids play in the spring season, we have teams returning to play the spring that did not play in the fall, but the played the previous spring. I need to fine from the registered teams what team played in the spring of say 2018 and capture their record so we have some knowledge of their ability-not done yet

    I am sorry for this long explanation, but I think is good to know when trying to help to understand the scope of the project.

    As usually Thank you,

    Kosta


    Vforum

    Monday, December 16, 2019 11:55 AM
  • Option Explicit is not mandatory, but it is useful because it forces you to declare all variables, and if you misspell the name of a variable, you will most probably get a warning. Here is a simple example:

    Sub Test()
        Dim myNum As Long
        myNum = 5
        MsgBox "The result is " & 3 * myNun
    End Sub

    If you do not have Option Explicit, myNun will be valid, but undefined, so you will get an unexpected/undesired result:

    But if you have Option Explicit at the top of the module, you'll get an error message:

    As you see, the item causing the problem is highlighted, making it easier to see what's wrong and to correct it.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 16, 2019 12:39 PM
  • I do understand

    The problem I have is to make a decision what to declare it

    I understand that every declaration hold a memory so what do I do? long, Boolean, string or anything ales.

    For example I have to extract the right two characters from a text the outcome is a string or a number it happens to be in my case a number but some time is a text, and becouse the number is not bigger then 10 I will asume is a Boolean.

    Yes I have a list of all tose declaration but some of them don't understand

    like the problem we had with workbook or worksheet and what is the deference from workbook then workbooks   

     and worksheet then worksheets.

    I am getting better but will take time and I have to do this constantly that I will not happen becouse once this projects is finish I will stop working on VBA until something ales comes up by that time I will forget all what I learned so far it's unfortunate but is reality.

    Hans you help me allots and for that a big Thank you.

    Kosta 

     

    Vforum

    Monday, December 16, 2019 1:02 PM
  • I have another question

    i have a form

    and has a search button do i have to use the click function or i can use something ales to let me know the button is clicked

    I need to bring the code into the main program so the user has to respond to one input box with all questions checked

    they way is now i have to open another box to ask more questions.

    please let me know.

    Kosta 

     

    Vforum

    Monday, December 16, 2019 1:08 PM
  • You can create a userform with text boxes, combo boxes, check boxes and option buttons to let the user specify information.

    Create a command button on the userform (with caption OK or similar), and in the on click event procedure of the command button, check whether all required information has been entered. If not, exit the procedure. If all is OK, process the information the way you want.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 16, 2019 1:22 PM
  • So question:

    1) can I use the form that I already have.

    2) how do i use the click event in my code? without  using privet sub click event

    3) if a can how 

    kosta


    Vforum

    Monday, December 16, 2019 2:37 PM
  • 1) "The form that you already have" - is that a userform? If so, yes, you can use that.

    2) An on click event procedure is the way to react to clicking a command button.

    3) ?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 16, 2019 2:49 PM
  • something like this perhaps 

    If Application.Caller = "CMD1" Then
            MsgBox "true"
        Else
            MsgBox "False"
       End If

    I don't want to use the click event


    Vforum

    Monday, December 16, 2019 2:59 PM
  • What is your form?

    1) A userform created in the Visual Basic Editor?

    or

    2) Part of a worksheet?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 16, 2019 3:16 PM
  • I use the form in change The file I have send you has the form Kosta

    Vforum

    Monday, December 16, 2019 3:32 PM
  • You want to search when the user clicks CMD1, so you do need

    Private Sub CMD1_Click()

    That is the way you handle a command button being clicked.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 16, 2019 3:47 PM
  • So let’s I go trough this I am running my code the code calls for the form to open Then after the check mark selection click the the button activate and the click privet sub And then how I get back to my code and continue from the spot I call the form I don’t want to start the code from the beginning. Is that something that can be done Kosta

    Vforum

    Monday, December 16, 2019 4:10 PM
  • You'll have to place the code that you want to execute after the user has made selections on the form in the Private Sub CMD1_Click() event procedure.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 16, 2019 7:52 PM
  • That is a problem.

    ok is any way to bring the variant from privet sub to another macro.

    like the two variant "wbDS" and "wbRT"

    or i have to create them in the macro

    that will be difficult as i am opening the files in the privet sub. any suggestions.

    Kosta

     

    Vforum

    Monday, December 16, 2019 10:03 PM
  • You can do the following:

    • Declare wbDS and wbRT as public variables at the top of a standard module, above all procedures (Subs):

      Public wbDS As Workbook
      Public wbRT As Workbook

    • Remove the declarations of wbDS and wbRT from all procedures, so that the only place where they are declared is the place mentioned above.
    • You can now use these variables throughout your code in this workbook.
    • You can do the same for other variables that you need to share between multiple procedures.
    • You can call another procedure from Private Sub CMD1_Click() as follows:

      Call MyMacro

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 16, 2019 10:15 PM
  • I will do it I have done this before but I had no success. Thank you Kosta

    Vforum

    Monday, December 16, 2019 10:30 PM
  • it worked thank you I just learn a new trick.

    Kosta


    Vforum

    Monday, December 16, 2019 10:58 PM
  • Good Morning Hans

    I need some clarification so I can understand how it works.

    The first think I am doing trying to capture the name and extension of the active workbook so later one I can point to that workbook by using

    “wbRAURT = ActiveWorkbook.name”  (this line of code is in the user form)

    then I am using this to create the variable wksRT

     “Set wksRT = wbRAURT.Worksheets("Register Teams")”  (this line of code is in the user form)

    And then I am using this code to move the cursor to cell “A1” of that workbook.

    “wksRT.Range("A1").Select”  (this line of code is in macro “search”)

    that line of code give me a problem

    So, I did “set wbRAURT = ActiveWorkbook.name “ (this line of code is in the user form)

    that gave me a problem did not like the “.name”

    I remove the “.name” and I end up with this

    “set wbRAURT = ActiveWorkbook”  (this line of code is in the user form)

    That seems like it worked

    As the things progresses the code opens other workbooks the active workbook is not the active workbook anymore, as the active workbook that I start with.

    So, I get an error when I get to this line of code

    “wksRT.Range("A1").Select” ( this line of code is in macro “search”)

     

    If before runs that part of the code I make the active workbook the correct one, then it works and moves the cursor to “A1”

    This is the declarations

    Public wbRAURT  As Workbook

    Dim wksRT As Worksheet (this declaration is in macro “search”)

    I need to understand the mechanics of this.

    1) First why doesn’t like the ".name"

    2) Why doesn’t find the workbook when is not the active workbook

    3) How can I make sure that always finds the workbooks even if they are in defriend directory

    Can you help me to understand what is really happening?

    I hope I explained my self clearly

    Thank you,

    Kosta


    Vforum

    Wednesday, December 18, 2019 11:09 AM
  • 1) wbRAURT is a Workbook object.

    ActiveWorkbook.Name is a text string, not a Workbook object, so

    wbRAURT = ActiveWorkbook.Name

    and

    Set wbRAURT = ActiveWorkbook.Name

    cause an error: a workbook is not a text string.

    Set wbRAURT = ActiveWorkbook

    does work: you assign a workbook to a variable of type Workbook.

    2) You can only select cells or ranges in the active worksheet of the active workbook. So if you need to select something in wksRT, you must make sure that wbRAURT is the active workbook and that wksRT is the active worksheet:

    wbRAURT.Activate
    wksRT.Select
    wksRT.Range("A1").Select

    By the way, in most VBA code, it is not really necessary to select cells if you want to manipulate them. For example, instead of

    wksRT.Range("A1").Select
    Selection.Copy

    you can use

    wksRT.Range("A1").Copy

    This copies the range to the clipboard without selecting it, so it works even if wksRT is not the active sheet, and if wbRAURT is not the active workbook.

    3) I assume that you know where you stored the workbooks...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 18, 2019 11:53 AM
  • hi Hanse and thank you as usual now that i had my morning coffee i will try to understand all the info you have send me.

    I do know where i store the workbooks.

    The reason I ask all this question is becouse i am trying to get values from a deferent workbooks compare them and then store them in a deferent workbook

    I need to do this with out running in all this problems of pointing to the correct workbook.

    I am spending to much of time getting this write.

    I know what to do with logical aspect of the code but when I get to the pointing aspect I get in trouble.

    like pointing to workbook range cells  or workbook sheets renge cells.

    Kosta


    Vforum

    Wednesday, December 18, 2019 2:31 PM
  • hear is wher I am confused

    the set part is working that means wbRAURT is good

    But when gets to "wbRAURT.Active" I get an error 438 Object doesn't support this property or method why not?  

        Set wksRT = wbRAURT.Worksheets("Register Teams")
        Set wksURT = wbRAURT.Worksheets("Unregister Teams")
        wbRAURT.Active
        wksRT.Select
        wksRT.Range("A1").Select

    Kosta


    Vforum


    • Edited by Kosta si Wednesday, December 18, 2019 3:07 PM
    Wednesday, December 18, 2019 3:02 PM
  • Not

    wbRAURT.Active

    but

    wbRAURT.Activate


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 18, 2019 4:11 PM
  • Is something that Option Explicit will catch? 

    Kosta


    Vforum

    Wednesday, December 18, 2019 4:57 PM
  • No, since Active is not a variable.

    But if you declare wbRAURT explicitly as a Workbook, and type wbRAURT followed by a dot (point, period), the Visual Basic Editor will pop up a list of available properties and methods:

    As you see, Activate is among the available methods, but Active is not.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 18, 2019 7:52 PM
  • Hans, good morning

    is this syntax correct about the font syntax the ones before I know is correct

    wksRT.Range("a" & k, "o" & k).PasteSpecial xlPasteValues Font.color=vbRed Bold=True

    Let me know please 

    this is the whole code

    wbLDS.Worksheets("sheet1").Cells(RAURT, 1).Resize(1, 15).Copy
    wksRT.Range("a" & k, "o" & k).PasteSpecial xlPasteValues Font.color=vbRed Bold=True
     Application.CutCopyMode = True

    Vforum

    Friday, December 20, 2019 12:34 PM
  • I apologize for the late reply, the "unread" count was 0 until a few minutes ago.

    You cannot do it like that; PasteSpecial and Font are two separate things: one is a method (an action), the other is a property. Similarly, Color and Bold are two separate properties. Do it like this:

    wksRT.Range("a" & k, "o" & k).PasteSpecial xlPasteValues
    wksRT.Range("a" & k, "o" & k).Font.Color=vbRed
    wksRT.Range("a" & k, "o" & k).Font.Bold=True

    or, if you prefer:

    With wksRT.Range("a" & k, "o" & k)
        .PasteSpecial xlPasteValues
        .Font.Color=vbRed
        .Font.Bold=True
    End With

    or even

    With wksRT.Range("a" & k, "o" & k)
        .PasteSpecial xlPasteValues
        With .Font
            .Color=vbRed
            .Bold=True
        End With
    End With


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, December 20, 2019 8:48 PM
  • thank you,

    Kosta


    Vforum

    Sunday, December 22, 2019 10:52 AM
  • Hans good morning.

    I have a question.

    I wrote this program that goes trough 1380 records and checks them against 1271 records and that takes about 5 minutes I don't know if a shorter version of what I am doing?

    How does excel finds the data records so fast with out going through a loop rutin?

    I have two loop routines on going step by step in the first data(that is six columns long) recording the search data then is going trough step by step compering the data to each row of the second data it's almost 1.75 million searches I put a sample below of the search criteria below    

    But say I need to check another date base with the leftover date that did not match I am doing another search that will take about 5 more minutes.

    The question is when the first search is finish how do I save the counters and some information if I decide that I need to run the second search without running the first search.

    Thank you

    Kosta

        L = 2
        k = 2

        For x = 2 To lastrowDS 'Finding the information for search criteria in workbook "Division Standings"
        GES = wbDS.Worksheets("sheet1").Range("A" & x) 'Gender
        TYS = wbDS.Worksheets("sheet1").Range("B" & x) 'Team year
        CIS = wbDS.Worksheets("sheet1").Range("D" & x) 'Club Id
        CNS = wbDS.Worksheets("sheet1").Range("E" & x) 'Club name
        TIS = wbDS.Worksheets("sheet1").Range("F" & x) 'Team ID
        TES = wbDS.Worksheets("sheet1").Range("G" & x) 'Team name

        For xx = 2 To lastrowRT 'Finding the information for search criteria in workbook "Registewr teams"
        GER = wbRT.Worksheets("sheet1").Range("A" & xx) 'Gender
        TYR = wbRT.Worksheets("sheet1").Range("B" & xx) 'Team year
        CIR = wbRT.Worksheets("sheet1").Range("D" & xx) 'Club Id
        CNR = wbRT.Worksheets("sheet1").Range("E" & xx) 'Club name
        TIR = wbRT.Worksheets("sheet1").Range("F" & xx) 'Team ID
        TER = wbRT.Worksheets("sheet1").Range("G" & xx) 'Team name


        'Finding a match between Division standings and register team workbooks
        If A = 1 And GES = GER Then 'Gender "A" checkmark selection 
        ElseIf Not A = 1 Then
        Else
        GoTo Unregister:
        End If
        If B = 1 And TYS = TYR Then 'Team year 
    "B" checkmark selection 
        ElseIf Not B = 1 Then
        Else
        GoTo Unregister:
        End If
        If C = 1 And CIS = CIR Then 'Club id 
    "C" checkmark selection 
        ElseIf Not C = 1 Then
        Else
        GoTo Unregister:
        End If
        If D = 1 And CNS = CNR Then 'Club name 
    "D" checkmark selection 
        ElseIf Not D = 1 Then
        Else
        GoTo Unregister:
        End If
        If E = 1 And TIS = TIR Then 'Team id 
    "E" checkmark selection 
        ElseIf Not E = 1 Then
        Else
        GoTo Unregister:
        End If
        If F = 1 And TES = TER Then 'Team name 
    "F" checkmark selection 
        ElseIf Not F = 1 Then
        Else
        GoTo Unregister:
        End If
        'If it's a match, then copy the entire row of the match criteria from workbook "Division Standings" to workbook "register and unregister teams" sheet "Register teams"
        wbDS.Worksheets("sheet1").Cells(x, 1).Resize(1, 15).Copy
        wksRT.Range("a" & k, "o" & k).PasteSpecial xlPasteValues
        Application.CutCopyMode = True
        k = k + 1 'Incrementing the row position for the next copied information
        Exit For 'Exiting the loop
        GoTo nextloop:
        'Else
        'If it's not a match, then copy the entire row of the unmatched criteria from workbook "Division Standings" to workbook "register and unregister teams" sheet "Unregister teams'
    Unregister:
        If xx = lastrowRT Then 'Waiting to the end of search before copies the unmatched criteria
        wbDS.Worksheets("sheet1").Cells(x, 1).Resize(1, 15).Copy
        wksURT.Range("a" & L, "o" & L).PasteSpecial xlPasteValues
        Application.CutCopyMode = True
        L = L + 1 'Incrementing the row position for the next copied information
        End If
    nextloop:
        Next
        Next
     


    Vforum

    Sunday, December 22, 2019 11:46 AM
  • Also how can i transfer the date with all the properties intact if the text is bold and color red I will like to copy it that way.

    Kosta


    Vforum

    Sunday, December 22, 2019 1:37 PM
  • I found how to transfer the formatting of the text with.

    thank you any way.

    Kosta

    xlPasteFormats

    Vforum

    Sunday, December 22, 2019 2:31 PM
  • Hans I have an alignment program that I wrote it year ago.

    Then when got be above my head, I found a gentleman like you help me and finished the code and subsequently did some updates at my request then he retired so I hired some one through Upwork's, I straggled for two year with him and finally I got some kind working program from him but he was very sloppy.(the good part was that I did not pay lot's of money and then the league pay me back.

    Any way one of his task was if present with a page number to open a particular PDF file and go to that page.

    that doesn't work until today.

    I am going to take look and see if i can fix it (if can do a reverse engineering and find where he has that code)is any code that will do that? that is the question.

    Kosta

     

     

    Vforum

    Sunday, December 22, 2019 3:04 PM
  • What are A, B, C, D, E and F in your code?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 22, 2019 8:54 PM
  • I don't know how to interact with PDF files using VBA, sorry.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 22, 2019 8:55 PM
  • they are from user form checkmarks.

     
    Private Sub CMD1_Click()
    Set wbRAURT = ActiveWorkbook 'Capturing the active workbook's name and extensionn
    If CheckBox1.Value = True Then
    A = 1
    Else
    A = 0
    End If
    If CheckBox2.Value = True Then
    B = 1
    Else
    B = 0
    End If
    If CheckBox3.Value = True Then
    C = 1
    Else
    C = 0
    End If
    If CheckBox4.Value = True Then
    D = 1
    Else
    D = 0
    End If
    If CheckBox5.Value = True Then
    E = 1
    Else
    E = 0
    End If
    If CheckBox6.Value = True Then
    F = 1
    Else
    F = 0
    End If
    If CheckBox7.Value = True Then
    G = 1
    Else
    G = 0
    End If
    If A + B + C + D + E + F = 0 Then
    MsgBox "no selection was made"
    Exit Sub
    End If
    'Uncheck DataCheckBoxes
    CheckBox1.Value = False
    CheckBox2.Value = False
    CheckBox3.Value = False
    CheckBox4.Value = False
    CheckBox5.Value = False
    CheckBox6.Value = False
    Unload Selectwts

        '********************************
        '*Opening the files to be merged*
        '********************************
        strPath = ThisWorkbook.Path & "\"
        With Application.FileDialog(1)
            .Title = "Select the Division Standings workbook"
            .InitialFileName = strPath & "*.xlsx"
            If .Show Then
                Set wbDS = Workbooks.Open(.SelectedItems(1))
            Else
                Beep
                Exit Sub
            End If
            .Title = "Select the register teams workbook"
            .InitialFileName = strPath & "*.xlsx"
            If .Show Then
                Set wbRT = Workbooks.Open(.SelectedItems(1))
            Else
                Beep
                Exit Sub
            End If
           If G = 1 Then
            .Title = "Select Last years Springs Division Standings workbook"
            .InitialFileName = strPath & "*.xlsx"
            If .Show Then
                Set wbLDS = Workbooks.Open(.SelectedItems(1))
            Else
                Beep
                Exit Sub
            End If
            End If
       
        End With
        '*************************************
        '*finding the last row for both files*
        '*************************************
        lastrowDS = wbDS.Worksheets("sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row 'Finding the last row that has a data in workbook "Division Standings"
        lastrowRT = wbRT.Worksheets("sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row 'Finding the last row that has a data in workbook Register teamss"
        If G = 1 Then
        lastrowLDS = wbLDS.Worksheets("sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row 'Finding the last row that has a data in workbook "Division Standings"
        End If

    Call findreregisterdteams
    End Sub
      

    Vforum

    Sunday, December 22, 2019 11:09 PM
  • What you are trying to do is very complicated. Whichever way you do it in Excel will take a lot of time.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 23, 2019 11:46 AM
  • That is ok I do this twice a year no problem. My question was can I retained the veritable information so I can run more variations if have to Without doing the search again. I need to to the first search and then I have to see if I have to do more. Mosta

    Vforum

    Monday, December 23, 2019 12:34 PM
  • I'm afraid I don't understand.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 23, 2019 1:42 PM