Posts by Justin Doward

    Did you try the code? you should be able to replace the COPY and NOT COPY with the names of the columns you want to copy and not copy.

    change the number of combined copy/not copy in line 10:

    Code
    1. Dim ColStr(number of copy/not copy) As Variant

    Then build the array as shown (i.e. ColStr(1) = COPY etc)

    Then change line 32:

    Code
    1. If Application.Match(c.Value, ColStr) < 6 Then

    to reflect the number of COPY (i.e. there are 5 copy in your example, so < 6 in an array of 8, the array starts at 0 hence 7 is used in the DIM line.)

    line 30:

    Code
    1. If UBound(Filter(ColStr, c.Value)) = -1 Then ws1.Range(ws1.Cells(c.Row, c.Column), ws1.Cells(LstRw, c.Column)).Copy (ws2.Cells(1, DCol))

    copies any column that has a header that is not in the array.


    This achieves what you have in your example EXCEPT it also copies the column with header "Unknown62" which is not copied in your expected result.


    What I need to know is what identifies Unknown62 from the other Unknown header columns?

    This does most of what you want I think, in order to avoid copying "Unknown62" I need to know what the conditions are to avoid it... is it simply that it follows Copy4 or is there another way to identify this particular unknown when compared to the others?


    All I can suggest in that case is to make an array of the known names and then test if the column header has one of the known names, if not then offset by 1. the problem will be determining whether to offset by +1 or -1. I will have a think on it tomorrow. It is an unusual situation you are describing.

    You would do the offset by adding or subtracting 1 to the match as that is where the column is being determined, let me know if you get stuck but include an if statement such that if instr(ColStr(x), "Unknown") then ... etc.

    You can try this sort of idea, runs on your example:


    It runs a little slowly and could be sped up in a couple of ways, it will also throw out the order of your columns if you have single columns between the ranges.

    What is the information you have that makes you decide to copy the columns? are they always the columns that you have marked, or are the columns variable? You need to describe what the decision process is that you follow to select the column to copy, then just translate that into some code. Are the first and last you mention the values in the columns, if so what are the first and last going to be or is that also variable?

    Ah sorry, I switched back and forth from A1/A2 and must have forgotten to change the +1.


    I would normally use the match function rather than find to return the row number,


    Code
    1. Cl = application.match(Me.Lo_TextBox1.Value, searchRng ,0)


    It is simpler than the find function, I think that is where the +1 came from.


    HTHs

    Hi CR,


    your problem is that the find function is returning a number not a range, modify the code to dim as long, remove the set from "set cl" and add 1 to cl then remove .row from cl.row and it should work.


    Not sure why it appears to work for the other rows.

    Justin

    You can try this in the sheet code for the Inputs page:

    ```

    Note that I would usually just do .entirerow.hidden = true rather than the RowHeight = 0 but the result is the same.

    Hi Bob,


    I have misinterpreted what you are trying to do a couple of times, I was unaware of the function of ".link", all it does is paste a formula. Is this what you are after:


    Code
    1. Sub LinkVIX()
    2. Dim ws1 As Worksheet: Set ws1 = ActiveSheet
    3. Dim ws2 As Worksheet: Set ws2 = Sheet2
    4. ws1.Range("O5").Formula2 = "=" & ws2.Name & "!$F4"
    5. End Sub

    I.e. if this code is on a button on a sheet, then when the button is pressed it will place a formula in O5 linking to sheet2 F4.

    I think you are creating named ranges? There is no loop in the code you are using, I am not exactly sure what you are trying to do but I think it is something like this:


    Code
    1. Sub kre3()
    2. Dim rw As Integer, cl As Integer
    3. rw = 1
    4. cl = 2
    5. For rw = 1 To 10
    6. ActiveWorkbook.Names.Add Name:=Sheet1.Cells(rw, 1).Value, RefersTo:=Sheet1.Cells(rw, 2)
    7. Next rw
    8. End Sub

    sorry CR, looks like I pointed you in the wrong direction, I had a play with those methods and neither way worked for me either.


    It does work as follows:


    sorry about that.

    I would do it by returning the row value (c.row) in a .range(.cells) line.


    Code
    1. If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 And c.Value <> vbNullString Then
    2. MyRw = sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    3. sh2.Range("A" & MyRw).Resize(1, 4) = c.Resize(1, 4).Value
    4. sh2.Range(cells(MyRw, 5), cells(MyRw, 6)) = sh1.range(sht1.cells(c.row, 18), sh1.cells(c.row, 19))
    5. End if


    I have not tested it but that is the approach I would take, if you cannot get it to work let me know. You could probably also use the resize approach but I am not so familiar with this syntax.


    Code
    1. If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 And c.Value <> vbNullString Then
    2. MyRw = sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    3. sh2.Range("A" & MyRw).Resize(1, 4) = c.Resize(1, 4).Value
    4. sh2.Range(cells(MyRw, 5)).resize(1, 2) = sh1.range(sht1.cells(c.row, 18)).resize(1, 2)
    5. End if

    Hi DG,


    I do not think it is possible to determine the existence of the triangle but it is possible to determine the presence of the errors that lead to the triangle. In excel options under formulas and error checking rules there are all the types of error that lead to the green triangle. I believe that you would need to build error catching for each of the selected error types into your code.

    Just realised that


    Code
    1. If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 And c.Value <> vbNullString Then sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row)(2).Resize(1, 4) = c.Resize(1, 4).Value


    can replace:


    Code
    1. If c.Value <> vbNullString Then
    2. If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 Then sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row)(2).Resize(1, 4) = c.Resize(1, 4).Value
    3. End If

    NB: just saw Jolivanes response above so added this line - and I think it is an identical outcome.


    Hi crshuby,


    I thought your first solution was great, nice and to the point and it worked on my data. When I ran it on your data it seems to fail on the vbnullstring where no PO was present it created a single entry for the vbnull.


    Does this fix it?


    Apart from that I recommend referring to the sheet code.

    Hi crshuby,


    The code works fine with the data I tried, I would need to see more context of the data you are working with to troubleshoot it further.


    Don't forget to wrap your code in code tags (just highlight and press the </> button at the top of the message box).

    This will copy everything in a range from one sheet to one above the other in a second sheet.



    but it may cause problems if there are formulas referencing fixed areas