Data alignment via macro - help needed

[inactiveuserps19]

Sorry if something like this has been asked before - my knowledge is so minimal I wouldn't even be able to recognise a similar problem!

I'm struggling to create a macro to do something fairly complicated. I have a spreadsheet with various sheets and a lot of data, and what I want to do is this:

1) Look at row 2 of sheet 1.
2) Find the first value that is greater than x (call that cell X)
3) Align that row so that X is in column Y.
4) Re-align the corresponding row by the same amount in sheet 2 (I suspect this is the hardest part)
5) Repeat 1) to 4) for every row in sheets 1 and 2.


I'd be extremely grateful for any help!

 

[inactiveuserps19]

Fantastic - thank you very much. I'll let you know how it goes!

 

[inactiveuserps19]

Thanks again - it works brilliantly.

However I need one tiny alteration and I can't work out how to do it - I need to change it so that it ignores columns A to J, and stops at row 900. The values in all those cells don't need to be re-aligned, and they cause a mismatch error when the macro looks at them.

 

[inactiveuserps19]

I think I've worked out which bit needs adjusting:

[COLOR=navy]With[/COLOR] Sheets("sheet5")
    [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]Set[/COLOR] RngAc = Range(Range("A" & Dn.Row), Cells(Dn.Row, Columns.Count).End(xlToLeft))
</pre>

I need it to only look at the range J1:CO935. It must be easy to do...but I just can't work it out!

 

[inactiveuserps19]

Just realised it's a bit more complicated than that, because it's not the entire row that needs to be transposed - the first 8 columns need to be ignored. Any ideas?

 

[inactiveuserps19]

I think I've cracked it. Thanks again. No doubt I'll be back later...

 

[MickG]

If the starting column you want is column "K" and last row 900, then if yoe change The code as below:-
NB:- I'm sorry I left you with a starting point of sheet(5) (as Code), that was my testing sheet, obviously you've sorted that and changed to your actual sheet Name.
But for the benefit of continuity , I'll leave as is.
Change this:_

With Sheets("sheet5")
    Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With

To this :-

With Sheets("sheet5")
    Set Rng = .Range("K2:K900")
End With

For Information:-
The code will now l loop down column "K" and at each row it will set the range "RngAc" (That row of cells) from column K to the last filled cell in that row.
The code then Moves that range and sheet (2) Range as per "Dialog Box" entry, then moves to the next row of column "K" and repeats the operation.

 

[inactiveuserps19]

For some reason, it's copying the actual values into the second sheet, instead of just repeating the shift. Any ideas? I may have edited something important - what's the relevant piece of code?

Thanks!

 

[MickG]

I think that was a bit of an oversight on my part, try this:-
NB:- Sht names altered:- 1st Data sheet = Sheet(1)
2nd sheet = Sheet(2).

[COLOR="Navy"]Sub[/COLOR] MG23Jun16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] RngAc [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Message, Title, Default, MyValue
[COLOR="Navy"]Dim[/COLOR] P1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
'[COLOR="Green"][B] This means the column(26) and the Aligning number is  "15"[/B][/COLOR]
Message = "Enter values as Align,Column/Number i.e. 26/15"
Title = "Align Rows"
Default = ""
MyValue = InputBox(Message, Title, Default)
Col = Split(MyValue, "/")(0)
Num = Split(MyValue, "/")(1)
[COLOR="Navy"]With[/COLOR] Sheets("sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("K2:k900")
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]Set[/COLOR] RngAc = Range(Range("K" & Dn.row), Cells(Dn.row, Columns.Count).End(xlToLeft))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] RngAc
    [COLOR="Navy"]If[/COLOR] Ac >= Num [COLOR="Navy"]Then[/COLOR] P1 = Ac.Column: [COLOR="Navy"]Exit[/COLOR] For
[COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]If[/COLOR] P1 <= Col [COLOR="Navy"]Then[/COLOR]
        Temp = Application.Transpose(Application.Transpose(RngAc.value))
        Dn.EntireRow.ClearContents
        [COLOR="Navy"]Set[/COLOR] nRng = Dn.Offset(, Col - P1).Resize(, UBound(Temp))
        nRng = Temp
           [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
                [COLOR="Navy"]Set[/COLOR] RngAc = .Range(.Range("K" & Dn.row), .Cells(Dn.row, Columns.Count).End(xlToLeft))
                Temp = Application.Transpose(Application.Transpose(RngAc.value))
                .Cells(Dn.row, 1).EntireRow.ClearContents
                [COLOR="Navy"]Set[/COLOR] nRng = Dn.Offset(, Col - P1).Resize(, UBound(Temp))
                .Range(nRng.Address) = Temp
          [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[inactiveuserps19]

Excellent! It's all working beautifully now. Thanks once again!

This is probably quite easy, but if it's not, please don't spend any time working it out for me, because I can just do this manually.

Is there an easy way to automate loading a file, running my super-long macro (this new bit is only one small part!), saving the changes, opening another file, running the macro, and repeating the process several times?