Posts by lizzie5

I have a macro below that’s used for converting/displaying data in a specific format. There are two triggers in the macro - it takes a specific course of action based on the value of another cell (AF1). If cell AF1 = 1 and it runs the code I have underneath that If statement, it takes a LONG TIME to run. If cell AF1 = 2 then the code runs in a reasonable amount of time.

If I don’t touch my computer at all, it takes at least 7 minutes to run. If I have a lot of applications going or try to do something else, it nearly freezes and takes much longer (just to note, the macro still runs successfully in that case).

Below is the code. I’m still a macro novice; is there more efficient code I can use that would produce the same results?

Sub EISpricecheck()
'copies EIS tab pricing contents, pastes on CHECK tab. Here I will check to see if there are 1 or 2 sets of prices.
'This also concatenates discount key & applies/wknd/wkday values
 
'
Application.ScreenUpdating = False
    'clear contents from "check" tab, but leaves formulas on right intact
    Sheets("check").Select
    Columns("A:AB").Select
    Selection.ClearContents
   
    'copies "EIS" tab data and pastes to "check" tab
    Sheets("EIS").Select
    Columns("A:AB").Select
    Selection.Copy
    Sheets("check").Select
    Range("A1").Select
    ActiveSheet.Paste
   
   
'check if there are 1 or 2 sets of prices:
    'If 2 sets of prices, add concatenate formula in column D
    If Sheets("check").Range("AF1") = 2 Then
        'place concatenate formula in column D
        Range("D1").Select
        Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
            ActiveCell.FormulaR1C1 = ActiveCell.Offset(0, -3) & ActiveCell.Offset(0, -1)
            ActiveCell.Offset(1, 0).Select
        Loop
        Range("D:D").EntireColumn.AutoFit
    End If
   
    'If 1 set of prices, replace "weekend" with "prices" and "weekday" with BLANK, then add concatenate formula in col D
    If Sheets("check").Range("AF1") = 1 Then
        Dim Target, cell As Range
        Dim weekday, weekend, prices As String
            weekday = "weekday"
            weekend = "weekend"
 
            Set Target = Sheets("check").Range(Range("C1"), Range("C65536").End(xlUp))
            For Each cell In Target
                If cell.Value = weekday Then cell.Value = ""
                If cell.Value = weekend Then cell.Value = "prices"
            Next cell
       'add concat formula
        Range("D1").Select
        Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
            ActiveCell.FormulaR1C1 = ActiveCell.Offset(0, -3) & ActiveCell.Offset(0, -1)
            ActiveCell.Offset(1, 0).Select
        Loop
        Range("D:D").EntireColumn.AutoFit
    End If
   
   
Application.ScreenUpdating = True
 
End Sub

Re: vba code to paste special (values) using specific ranges

Any takers? I am still new to vba but I've come up with the following code to try to accomplish what I want. I am stuck at the offset part - the first loop works and does the paste special - but it stops when I'm trying to have it offset to go to the next Source and Destination ranges.

I get a run time error '1004': Activate Method Of Range Class Failed. The debugger goes to the first line where I try to offset. Instead of "activate" I have also tried "select" but that doesn't work either. Can anyone please help me figure it out?

Sub test()


Dim rSource As Excel.Range
Dim rSournce1 As Excel.Range
Dim rDestination As Excel.Range
Dim rDestination1 As Excel.Range




    Set rSource = Worksheets("FINAL").Range("D3:BY3")
    Set rDestination = Worksheets("Promotions").Range("A16")
    
    Set rSource1 = Worksheets("FINAL").Range("R2:AO2")
    Set rDestination1 = Worksheets("Promotions").Range("O15")
     
Application.ScreenUpdating = False






Do While IsEmpty(Worksheets("FINAL").Columns("C")) = False
        rSource.Copy
        Worksheets("Promotions").Select
        rDestination.Select
        Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
    
        rSource1.Copy
        rDestination1.Select
        Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
    
    rSource.Offset(2, 0).Activate
    rDestination.Offset(7, 0).Activate
    rSource1.Offset(2, 0).Activate
    rDestination1.Offset(7, 0).Activate
Loop




        




Application.ScreenUpdating = True




End Sub

Re: vba code to paste special (values) using specific ranges

Bump

Re: vba code to paste special (values) using specific ranges

Hi - I created a copy of my spreadsheet and trimmed it down as much as I could but I tried all I could think of and I can't get the size of the attachment small enough for ozgrid to let me upload it

I tried taking some screenshots and attached those instead. I had to trim those down to fit as well, I took two screenshots: one showing the first rows and columns from the FINAL tab, and one showing the first rows and columns from the PROMOTIONS tab. On the Promotions tab, I manually did a Paste Special for the first set of data to show what I'm looking for.

Please let me know if any additional clarification is needed. Thanks!

Hi – I need help with some code to paste special (values) from specific ranges on one worksheet to specific ranges on another worksheet. I feel as though I should be able to figure this one out but I’m struggling.

The origin of the data is on the tab titled “FINAL”, which consists mostly of VLOOKUP formulas. I need to copy from the FINAL tab and paste special (values only) onto the destination tab, titled “Promotions”.

The data on the FINAL tab will always be different in future samples, and will extend down to a different row every time depending on the data size – so the code should run as long as there is a value in column C on the FINAL tab.

The destination tab (“Promotions”) has some merged cells that the FINAL tab doesn’t have, so I’m not able to select all and paste special; it looks like I need code to go line by line and offset. There are two steps I need the code to do for each piece of data, here are instructions for the first step the code should do:

1a. FINAL tab: select and copy range D3:BY3
1b. Promotions tab: paste special (values) the selected range starting in cell A16
2a. FINAL tab: select and copy range R2:AO2
2b. Promotions tab: paste special (values) the selected range starting in cell O15


After that, the code will do the same thing but will offset going down to lower rows. On the FINAL tab it will offset 2 rows down to copy, then paste special on Promotions tab offsetting 7 rows down. If it helps to have it written out, I’ll write the next steps out like I did above:

3a. FINAL tab: select and copy range D5:BY5
3b. Promotions tab: paste special (values) the selected range starting in cell A23
4a. FINAL tab: select and copy range R4:AO4
4b. Promotions tab: paste special (values) the selected range starting in cell O22



I hope this all makes sense, please let me know if not. Any help would be greatly appreciated!!

Re: vba code to transpose a dynamic range of data to a new worksheet

Perfect! Thank you SO much for all of your help, I really appreciate it

Re: vba code to transpose a dynamic range of data to a new worksheet

Thank you. So far it looks good but I will check it in full detail.

What is the difference in this one - is it referencing anything in the worksheet or were you able to modify the code? Just wondering if there are any assumptions or things I should be aware of in the code, I don't fully understand all of it. Thanks!

Re: vba code to transpose a dynamic range of data to a new worksheet

Hi, I notice that you wrote "change" to the value for the range. Is it impossible to make it a dynamic range? The users that will be running this code will not have the knowledge to go in and update the code, I won't be able to have people go in and modify that number.

If it's not possible to make the range dynamic within the code, is it possible to have it reference a cell within the worksheet (such as cell AA2 from the "Set 1" tab). I have a formula in that cell to calculate the number in that range that seems to work, can that be used within the code so we can avoid changing the code itself every time you want to run it?

Thanks!

Re: vba code to transpose a dynamic range of data to a new worksheet

Hi jindon - thanks for your help!!! I tried your code on the data sample I sent you and it works great, HOWEVER - it doesn't seem to be dynamic as it doesn't work properly for a different data set. I attached a different data set for a second sample. In the previous sample, the range happened to be 7, but that range should be dynamic. In my second sample I attached here, the range happens to be 4.

When I try running the test code you provided on this second set of data the output is wrong. It seems to still go out to 7 columns, and the data outputted in columns A-B isn't accurate as well (it should be listed in groups of 3, one each for the "applies", "weekend", and "weekday" listed in the corresponding rows).

Does that make sense? Hopefully my attachment will help as well. Are you able to modify the code to account for a dynamic range?

Hi, I need help with code for a dynamic range of data. I thought I might be able to figure out something based on help I recently received for a similar issue, but this data set is different I haven't figured out how to solve this one.

I have attached my data sample here. I have a range of values in columns R and S that will change in my future data samples, so the code has to be based on the number of values in this dynamic range. The values in columns R-S are prices, and they are listed in decreasing face value. OR – a second option to base the dynamic range off of are the values in column A; this value is vertically listed repeatedly for as many prices listed in columns R-S. In my current data set attached, there are 7 prices in my dynamic range.

MY DESIRED GOAL: I need the vertical data in columns A-B, R-T to output horizontally onto the “EIS” tab based on the dynamic range. See “EIS” tab in my attachment for an example of what I want the data to look like in the end. My example output uses the first two items from the “Set 1” tab; I used a different color for each item only to better illustrate the sequence. The “discount_key” and “qualifier” need to be listed in columns A-B, respectively. In adjacent cells in column C, the words “applies”, “weekend”, and “weekday” should be hard-coded. In adjacent cells beginning in column D, the price range information should be outputted horizontally. The first of the three rows of data for each “discount_key” should reference column T from the “Set 1” tab (i.e. “Yes” or “No” – which explains whether a discount applies to that price). The second row should output the range of prices for the weekend, coming from “Set 1” tab column S. The third row should output the range of prices for the weekday, coming from “Set 1” tab column R. The code will then go to the next “discount_key” and “qualifier” and repeat the same steps.

This needs to continue until all rows on the “Set 1” tab (i.e. in my sample there are 722 rows of data, but the number of rows will be dynamic in future data) have been outputted into the grid on the “EIS” tab. All data will change in my future samples (except the constant that I know what type of data will be in each column), but the output needs to be based on the dynamic price range (or the number of repeated values listed in column A – whatever is easier to use). If the dynamic range is based on the number of prices listed in columns R/S, it will need to be able to handle up to 24 values maximum.

I don’t have code to work from that applies directly to this issue. All I have at this point are some formulas I have in my attachment on the “Set 1” tab in cells Y2:AA2 to calculate how many values are in the dynamic range. The formula for the # of unique values located in Z2 needs to be dynamic though.

I hope I was able to explain what I’m looking for, please let me know if not. Any help would be GREATLY appreciated!! Thank you!

Re: vba code to transpose dynamic ranges of data

Thank you!

Re: vba code to transpose dynamic ranges of data

Jindon – thank you SO much! I can’t tell you how thankful I am for your quick and brilliant help!

I am going through and checking the output for accuracy, so far everything looks great and I haven’t seen any issues.

I’m not sure if you/other posters in the forum ever explain what a code actually is doing – I am just curious if you could share any insight on the code itself. I haven’t seen anything like that before and don’t fully understand it. I am 100% happy as long as it works, it would just be nice to know more about it. BUT if you don’t normally do that really don’t worry about it, I won’t expect a response on this question. Either way, THANK YOU!!!

Re: vba code to transpose dynamic ranges of data

Almost. See below with correction:

If J = No, both No
If J = Yes and I = Yes, Yes in weekend, No in weekday
If J = Yes and I = No, No in weekend, Yes in weekday

Re: vba code to transpose dynamic ranges of data

Column I tells us if the performance_code is on a weekend or a weekday.

Column J tells us if the discount_key applies to that performance_code.


Here is some static code from my original post to help explain; I added verbal descriptions for each possible scenario:

‘if value in column I is NO then it’s a weekday event, so put NO in the weekend rowIf Range("I2") = "No" Then
    Range("O2").Value = "No" 
End If
 
‘if value in column I is NO, then it’s a weekday event AND
‘if value in column J is YES, then it applies, so put YES in the weekday row
If Range("I2") = "No" And Range("J2") = "Yes" Then
    Range("O3").Value = "Yes" 
End If
 
‘if value in column I is NO, then it’s a weekday event AND
‘if value in column J is NO, then it does not apply, so put NO in the weekday row
If Range("I2") = "No" And Range("J2") = "No" Then
    Range("O3").Value = "No" 
End If
 
 
 
‘if value in column I is YES, then it’s a weekend event, so put NO in the weekday row
If Range("I2") = "Yes" Then
    Range("O3").Value = "No" 
End If
 
‘if value in column I is YES, then it’s a weekend event AND
‘if value in column J is YES, then it applies, so put YES in the weekend row
If Range("I2") = "Yes" And Range("J2") = "Yes" Then
    Range("O2").Value = "Yes" 
End If
 
‘if value in column I is YES, then it’s a weekend event AND
‘if value is column J is NO, then it does not apply, so put NO in the weekend row
If Range("I2") = "Yes" And Range("J2") = "No" Then
    Range("O2").Value = "No" 
End If

Sorryif that wasn't clear, does it make sense now?

Re: vba code to transpose dynamic ranges of data

Hi jindon - wow, thanks for your help! So far I do see something that isn't working correctly though. It seems as though anything with a "yes" in column "I" isn't outputting in the grid properly.

Example 1: see the first discount_key from column A ("C151P2050B5378R"). After running the code - in the output grid see performance_code "EUC1118E" - the weekday & weekend output BOTH say "yes". There should never be a time when both the weekend and weekday output rows say "yes" at the same time since that's contradictory. In this specific case, based on the values in columns I-J, the grid should say: weekend "yes" / weekday "no"

Example 2: see the discount_key "G3D0116". After running the code - in the output grid see performance_code "EUC1118E" - here both weekend and weekday say "yes". In this specific case, based on the values in columns I-J, the grid should say: wekeend "no" / weekday "no"

Are you able to help modify the code to fix this? Thanks again.

Hi – I’m really hoping someone can help me with some code to display data in the format I’m looking for. I’m still learning about vba code and I’ve been stuck trying to find a solution, as the ranges in my data will change from week to week.

I have attached my data sample here. I have a range of values in column H that will change in my future data samples, so the code has to be based on the number of unique values in this dynamic range. The values in column A are also unique, they are listed vertically to correspond with each unique value in column H.


MY DESIRED GOAL: I am trying to have the vertical data in columns A, H-J output horizontally based on my dynamic range in row H. I used a formula to calculate the number of unique values in column H, but I still need to figure out how to make the formula dynamic (the formula is located in cell L1).

I created an example of what I want the data to look like in the end, you can see this to the right of column L. I have a grid where the unique values from column H are listed across the top starting in cell O1. Underneath that, there are two horizontal rows for each value in column A, one row is hard-coded titled “weekend” and the second row is hard-coded titled “weekday”. I used two different colors in attempt to better illustrate. The data from columns I-J should output horizontally in this grid, starting in cell O2 in my example. Once the first two rows of data have been populated in the grid, the code needs to know to move down to begin the next loop (i.e. move to O4-O5 to work on the data for the next unique value in column A, which starts in row 22 in my sample). This needs to continue until all rows (i.e. in my sample there are 1121 rows of data, but the number of rows will be dynamic in future data) have been outputted into the grid. The biggest problem I have is that this data sample will change from week to week, the only thing constant is that I know what type of data will be in each column, but the number of unique values in column H will always change.

Note: it doesn’t matter to me if the final data is outputted on the same tab like I have it now, or if it’s on a new tab.


CODE: I don’t have much code at this point since I’m not quite sure from what angle to come at this with, I just have a few very basic samples that I have come up with so far. Here is some static code for getting the values for O2-O3 based on the values in I2 and J2; this is just a model to work off of to build out the grid I need, again I need to figure out how to make it dynamic:

'if it's a weekday event
    If Range("I2") = "No" Then
    Range("O2").Value = "No"
    End If
    If Range("I2") = "No" And Range("J2") = "Yes" Then
    Range("O3").Value = "Yes"
    End If
    If Range("I2") = "No" And Range("J2") = "No" Then
    Range("O3").Value = "No"
    End If
   
    'if it's a weekend event
    If Range("I2") = "Yes" Then
    Range("O3").Value = "No"
    End If
    If Range("I2") = "Yes" And Range("J2") = "Yes" Then
    Range("O2").Value = "Yes"
    End If
    If Range("I2") = "Yes" And Range("J2") = "No" Then
    Range("O2").Value = "No"
    End If

I also have some code to transpose the number of unique values listed in column H to list them horizontally starting in O1, but my formula in L1 somehow needs to be modified to be dynamic.

Dim iNumEvents As Integer
 
    iNumEvents = Range("L1") + 1
   
    Range(Cells(2, 8), Cells(iNumEvents, 8)).Select
    Selection.Copy
    Range("O1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Range("O1").Select

Any help would be truly appreciated. This is my first time posting so please forgive anything I may have overlooked.