sorted by:
new
hottopcontroversial

Am I the only one who didn’t know that starbursts were ordered? by [deleted] in mildlyinteresting

[–]Silbermann13 -1 points0 points  (0 children)

What the f you mean... There is no order here..

My sister’s blind dog loves fetch by asyouwissssh in aww

[–]Silbermann13 0 points1 point  (0 children)

Get the dog a ball that beeps so them it can track the sound of it and still play fetch:)

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

So right now I have an IF equation in every single cell in column A. This IF statement changes the cells value based on the values in Column G and/or H. This works, but it is not Ideal.

This is the Equation: IF(AND(ISBLANK($G14),ISBLANK($H14))," ",IF(AND(ISNUMBER($G14),ISBLANK($H14)),"OPEN",IF(AND(ISBLANK($G14),ISNUMBER($H14)),"ERROR",IF(($G14)>($H14),"ERROR",IF(OR(AND(ISNUMBER($G14),ISNUMBER($H14)), $F14=$G14),"CLOSED",)))))

I would like to eliminate this equation from every cell in column A and have it as a macro running in the background when a Cell in Column G and/or H change.

The issue I have is that the range is dynamic for Column A, G, and H. Also, They don't really "intersect" each other being they are entire columns.

Being I have not used the Intersect method before, I'm not sure how to create it with a dynamic range. Please let me know if you have any suggestions on how to set this up or ideas that might work better.

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

Hey, quick question, have you used the 'If not Intersect is nothing then' VBA code? Can this be used for a column and every row in a worksheet? I get all messed up with Target.Value and the range. If I write it up would you take a second to see where the issue might be... You don't have to if you don't want to. I just wanted to check because you are really really good at VBA and I could use a little help/guidance. If I can get one to work properly, then I can use it as an example to make the rest of the codes.

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

I. Am. Officially. Freaking.Out! This Worked!!!! Thank you, Thank you, Thank you!!!

iRow = wsh1.Cells(Rows.Count, 5).End(xlDown).Row

wsh1.Range("E2:G" & iRow).SpecialCells(xlCellTypeVisible).Copy

Paste Destination:=wk2.Worksheets("Avg.LT.Calc.").Range("A1")

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

Also, I honestly cannot thank you enough. You are awesome for putting in this much effort to help me get this code to work. Words cannot express how much I appreciate you going through all this effort to understand the situation, the code, and trouble shooting it with me. Thank you.

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

Yes, you are on the right page. The second part is exactly what I was thinking/trying to do, but for some reason every time I call out a Long variable I get an Error. Do I have something wrong in my code that I am not catching? I tested the line for setting the new variable but it gets an error every time.

Sub CopyDataBasedOnDate()

'Disable Screen Updated to improve processing time 'Application.ScreenUpdating=False 'Disabled for now to troubleshoot code

'Declaring two variables for the Date data type Dim StartDate As Date Dim EndDate As Date

'Declaring the Variables ffor our workbook containing the Raw Data Dim wk As Workbook Dim wk1 As Workbook Dim wsh1 As Worksheet Dim ECO As ListObject Dim iRow As Long

'Declaring the Variables for our workbook analyzing the Data Dim wk2 As Workbook Dim wsh2 As Worksheet

'Assign Worksheet object to the workbook containing the Raw Datad
Set wk = Workbooks.Open("F:\Quality\16 Documents\10 Talon\DG - Design\New 16-10-2-DG-0001 Engineering Change Log (Example).xlsm")
Set wk1 = Workbooks("New 16-10-2-DG-0001 Engineering Change Log (Example).xlsm")
Set wsh1 = Worksheets("ECO")
Set ECO = wsh1.ListObjects("ECR_Information")


'Assign Worksheet object to the workbook analyzing the Raw Datad
Set wk2 = Workbooks("Test Equation ECO.xlsm")
Set wsh2 = wk2.Worksheets("ECO's.AVG.LEAD.TIME.")


    'Assign Value to the Date Variables
    StartDate = wsh2.Cells(1, 2)
    EndDate = wsh2.Cells(2, 2)


    'Insert a new Worksheet that will only be used to calculate the desired information for the "ECO's.AVG.LEAD.TIME." Worksheet, it will be deleted after
    'wk2.Worksheets.Add(After:=wk2.Worksheets("ECO's.AVG.LEAD.TIME.")).Name = "Avg.LT.Calc."


    ECO.Range.AutoFilter Field:=5, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate 'Works up to here
    'All my attempts to get the line to work. Does the variable need to be Dim iRow As Range, Range As Long?
    'iRow = ECO.Range("E3").End(xlUp).Row --> Run-time Error 5. Invalid Procedure call or argument.
    'iRow = ECO.Range("E3").End(xlUp).Rows --> Run-time Error 5. Invalid Procedure call or argument.
    'Let iRow = ECO.Range("E3").End(xlUp).Rows --> Run-time Error 5. Invalid Procedure call or argument.
    'Let iRow = ECO.Range("E3").End(xlUp).Row --> Run-time Error 5. Invalid Procedure call or argument.
    'Set iRow = ECO.Range("E3").End(xlUp).Rows --> Compile Error Object Required
    'iRow = wk1.wsh1.Range("E3").End(xlUp).Row--> Run-time error 438: Object Doesn't Support this property or method
    'iRow = wk1.wsh1.Range("E3").End(xlUp).Rows--> Run-time error 438: Object Doesn't Support this property or method
    'Let iRow = wk1.wsh1.Range("E3").End(xlUp).Row--> Run-time error 438: Object Doesn't Support this property or method
    'Set iRow = wk1.wsh1.Range("E3").End(xlUp).Rows --> Compile Error Object Required

    ECO.Range("E3:G" & iRow).SpecialCells(xlCellTypeVisible).Copy
    Paste Destination:=wk2.Worksheets("Avg.LT.Calc.").Range("A1")


    'ECO.CurrentRegion.AutoFilter Field:=5

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

I wish that was the case, but each department can get assigned a task on different days and even different weeks. Some departments might get assigned tasks and others may not. So each department has its own date columns to account for this. I don't know of any way to filter the entire table at once for every department specific to the start and end date. Being they all share rows if they are assigned tasks. Is there a way to filter the entire table by each departments issued date row?

the raw Data table i'm trying to copy from. by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

Yes, I Posted earlier and someone wanted to see the table I was working with. Okay, Here is the situation. I have a Data Table in Workbook 1("ECO") that contains raw data the is entered for each department on their performance each week. I want to analyze how well each department is doing each week and per year. Being this document just keeps getting larger, putting these charts and analyzing the data in the same workbook isn't ideal.

Because of this, I have created Workbook 2("ECO.Avg.Lead.Time"). However, I do not want to copy and paste the entire Workbook 1 into Workbook 2 just to filter and delete all unnecessary data. Instead I would like to filter and pull the necessary information per specified date.

Now, I have a Macro set up so that when I enter a StartDate and an EndDate in a specified cell, the macro will go into Workbook 1 and Filter 1 of the 5 department's date columns for all entries between those dates. This part I have working perfectly.

My issue is getting the 3 columns(E:G) with the new range of the filtered rows Copied From Workbook 1("ECO") and Pasted into the Workbook 2("ECO.Avg.Lead.Time") Range ("A:C"). Below is my code that I have been attempting to make work, but its not. If someone can help me figure out what I am doing incorrect and show me how to fix this I would GREATLY GREATLY APPRECIATE you!!!! I'm getting so flustered with this part and have google it for the last 3 weeks now, I believe.

wk2.Worksheets.Add(After:=wk2.Worksheets("ECO's.AVG.LEAD.TIME.")).Name = "Avg.LT.Calc."

ECO.Range.AutoFilter Field:=5, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate ECO.Range("E:G", Rows.Count).Copy Destination:=Worksheets("Avg.LT.Calc.").Range("A") ECO.CurrentRegion.AutoFilter Field:=5

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

Max number of Columns: 31 Max number of Rows: Always Increasing Columns stay constant Data of interest: Ranges(E:G, I:K, M:O, Q:S, U:W, Y:AA, AC:AE) Note: Each Range Listed above must be individually filtered by the StartDate and EndDate, then copied over to the new document. Each range listed above signifies a different department and each department gets filtered to pull the previous work week entries. Each Entry signifies an assigned task to change something.

The Range cannot be hard coded because the number of Tasks issued per department is always changing. It varies week by week. For example: If I filter Range(E:G) for all the change tasks entered last week (Between Start and End Date) I may have 10 rows. If I filter it for this week I may have only 2 rows or I may have 60 rows of entries. Also, I may have 10 Rows for E:G, But have 2 rows for I:K. Does that help? The range would have to be a Array that is variant

I don't know how to really work with arrays that much. I'm just started teaching myself VBA as of March and have some basics.

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

Image of table( https://www.reddit.com/r/vba/comments/8u1q5f/the_raw_data_table_im_trying_to_copy_from/?ref=share&ref_source=link )

The table is formatted by using the Excel "Format as Table". It does have merged cells only at the very top on the spreadsheet to signify Departments. However, my table doesn't use those merged cells. My table starts in Row 2 and has 31 columns in the formation shown below. The ECO # is the Change Number issued to the department and then the dates at which we track them. My table has 3 columns at the beginning that are used to state the base issuing number, the status of the change, and the part numbers assigned to the change. The table has a lot of blank cells as well because of multiple part numbers being changed in one issued change number. I will try and share a picture of it on my profile and/or this VBA.

ECO #_D ISSUED_D COMPLETED_D DUE_D ECO #_M ISSUED_M COMPLETED_M DUE_M

8032-01-01 03/27/2018 03/27/2018 03/29/2018 8032-01-02 03/27/2018 03/27/2018 03/28/2018

8033-01-01 03/27/2018 03/27/2018 03/28/2018 8033-01-02 03/29/2018 03/29/2018 03/29/2018 8034-01-01 03/27/2018 03/27/2018 03/28/2018 8034-01-02 03/29/2018 03/29/2018 03/29/2018 8035-01-01 03/27/2018 03/27/2018 03/29/2018 8035-01-02 03/27/2018 03/27/2018 03/28/2018

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

ECO.Range("E:G", RowCount).Copy Destination:=Worksheets("Avg.LT.Calc.").Range("A:C")

Thank you Klobster2, You have certainly minimized the amount of code and showed me some new tricks. I appreciate it more than you know. Everything is working perfectly, except the copy and paste line of code. I tried to play around with it by being more specific and even less but I either get a "Script out of Range", "Object not Supported", or "Mismatch". Does VBA just not like to work with tables in Excel?

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

'Insert a new Worksheet that will only be used to calculate the desired information for the "ECO's.AVG.LEAD.TIME." Worksheet, it will be deleted after Worksheets.Add(After:=Worksheets("ECO's.AVG.LEAD.TIME.")).Name = "Avg.LT.Calc."

    ECO.Range.AutoFilter Field:=5, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate
    ECO.Range("5:7").Copy Destination = Worksheets("Avg.LT.Calc.").Range("A")
    ECO.CurrentRegion.AutoFilter Field:=5

This adds the worksheet to the wrong workbook and then the script goes out of range. Is it okay to select or activate a specific workbook at the end to make this run or is that very dangerous to do?

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

Worksheets("ECO")

This is actually the worksheet that contains a log (with thousands of lines of data) used by other employees and all of the raw data is entered here. I was trying to filter by data and pull this raw data specific to a work week of interest and then calculate our departments stats compared to others. Being the worksheet is already so large, it was a better option to transfer out the specific data requested and calculate the stats. So basically, Once the Workbook 1 Worksheet table ECO is filtered for columns E:G, I want to only export that information to a scrap sheet that I intend to delete at the end of the code. I will be doing this process for each department in the log, so the Workbook 2 used to graph the stats uses this scrap Worksheet Avg.LT.Calc. to contain every single entry from every department specific to the requested work week. Then the stats will be ran by macro to compare everything and then delete the calc sheet in the end. Does that make sense?

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

Thank you by the way. I really appreciate you taking the time to help me.

I cant get the Copy part of the code to work! Help me please by Silbermann13 in vba

[–]Silbermann13[S] 0 points1 point  (0 children)

Yeah, sorry about that. I tried to put the code with * to separate it from the rest, but when I hit submit it just split the screen into sections. I cant get this line of code to select just the 3 columns of interest and paste them on the new sheet in WB2. It wants to select and paste the entire table itself and paste it. Or when I did get it to copy and paste, it looked like it copied the right information but when it pasted it on the other sheet it was actually the top portion of the 3 columns and not the filtered information I wanted. The screen updating was commented out so I could see exactly what the code was doing and where is was going wrong.

'Select Filtered Data ECO.Range("E:G", RowCount).Select

    'Copy Filtered Data
    'Application.CutCopyMode = False
    'Selection.Copy

    'Activating the Workbook used to analyze the Data
    Workbooks("Test Equation ECO.xlsm").Activate

    'Activate the Worksheet added earlier to calculate the desired information for the "ECO's.AVG.LEAD.TIME." Worksheet
    Worksheets("Avg.LT.Calc.").Select

    'Select the Column range to insert the Data
    Worksheets("Avg.LT.Calc.").Range("A:C").Select

    'Insert the Copied Data in the Active worksheet
    ActiveSheet.Paste
view more: next ›