PDA

View Full Version : [SOLVED] Need VBA out- put to different location on worksheet.



estatefinds
03-16-2016, 01:09 PM
I have data that is placed in columns ABCDE and need this data to be moved into Columns BCDEF which will then will have to move the macro Sub M_snb() which it look at the the data and what ever is uncolored currently result in columns GHIJK row 46. so I need the VBA to results, instead to be placed in columns AL AM AN AO AP ROW 9; this will be its permanent position. I had attached file. Thank you!

p.s. also the data in the columns ABCDE that will be placed in to Columns BCDEF will change meaning data will be added so the macro has to be able follow where the uncolored values are any where in the columns. the attachment gives a clear picture. please,
Thank you!

PAB
03-17-2016, 02:16 AM
Hi Dennis,

Change the Macro to this...


Sub M_snb()
sn = Cells(9, 2).CurrentRegion

For j = 1 To UBound(sn)
For jj = 1 To UBound(sn, 2)
If InStr(c00, " " & sn(j, jj) & " ") <> 0 Then sn(j, jj) = "~"
c00 = c00 & " " & sn(j, jj) & " "
Next
Next

For j = 1 To UBound(sn)
sp = Filter(Application.Index(sn, j), "~", 0)
For jj = 1 To UBound(sn, 2)
sn(j, jj) = ""
If jj - 1 <= UBound(sp) Then sn(j, jj) = sp(jj - 1)
Next
If UBound(sp) > -1 Then c01 = c01 & " " & j
Next
sp = Application.Transpose(Split(Trim(c01)))

Cells(45, 7).Resize(UBound(sp), UBound(sn, 2)) = Application.Index(sn, sp, Array(1, 2, 3, 4, 5))
End Sub

I hope this helps!

estatefinds
03-17-2016, 04:44 AM
thank you!, ok so I moved the DATA from ABCDE to BCDEF now I need the results of the data instead of placed into GHIJK starting on row 46 instead placed to the Columns AL AM AN AO AP on row 11. also can thus macro be made so where ever the data is in this columns the Macro will still run? I ask cause more data will be added and the unfilled numbers will change. please,
Thank you

PAB
03-17-2016, 05:37 AM
Hi Dennis,

Give this a go...


Sub M_snb()
sn = Cells(9, 2).CurrentRegion

For j = 1 To UBound(sn)
For jj = 1 To UBound(sn, 2)
If InStr(c00, " " & sn(j, jj) & " ") <> 0 Then sn(j, jj) = "~"
c00 = c00 & " " & sn(j, jj) & " "
Next
Next

For j = 1 To UBound(sn)
sp = Filter(Application.Index(sn, j), "~", 0)
For jj = 1 To UBound(sn, 2)
sn(j, jj) = ""
If jj - 1 <= UBound(sp) Then sn(j, jj) = sp(jj - 1)
Next
If UBound(sp) > -1 Then c01 = c01 & " " & j
Next
sp = Application.Transpose(Split(Trim(c01)))

Cells(11, 38).Resize(UBound(sp), UBound(sn, 2)) = Application.Index(sn, sp, Array(1, 2, 3, 4, 5))
End Sub

I hope this helps!

estatefinds
03-17-2016, 05:53 AM
Thank you! it is now where it needs to be Thank you!!!!

PAB
03-17-2016, 05:58 AM
You're welcome, thanks for the feedback.

estatefinds
03-18-2016, 02:25 PM
i am having trouble with this it is taking the numbers in column A and placing with data that is only supposed to be data in columns BCDEF can you help me fix this? please?

estatefinds
03-18-2016, 02:30 PM
i dont understand what happend?

estatefinds
03-18-2016, 02:42 PM
I got it working again Thanks

estatefinds
03-18-2016, 02:56 PM
okay i need help this is not working the way its supposed to.

the only data is supposed to be in columns AL:AP are from BCDEF. the column A gets left alone is that is used for another macro. Please help me Correct this!
Thank you

estatefinds
03-18-2016, 03:52 PM
can this code be re written to avoid the data in column A? reason is that data in column A is what is used for my highlighting if data in BCDEF. but when i run the macro Sub M_snb() sn = Cells(9, 2).CurrentRegion

For j = 1 To UBound(sn)
For jj = 1 To UBound(sn, 2)
If InStr(c00, " " & sn(j, jj) & " ") <> 0 Then sn(j, jj) = "~"
c00 = c00 & " " & sn(j, jj) & " "
Next
Next

For j = 1 To UBound(sn)
sp = Filter(Application.Index(sn, j), "~", 0)
For jj = 1 To UBound(sn, 2)
sn(j, jj) = ""
If jj - 1 <= UBound(sp) Then sn(j, jj) = sp(jj - 1)
Next
If UBound(sp) > -1 Then c01 = c01 & " " & j
Next
sp = Application.Transpose(Split(Trim(c01)))

Cells(11, 38).Resize(UBound(sp), UBound(sn, 2)) = Application.Index(sn, sp, Array(1, 2, 3, 4, 5))
End Sub
it picks up the data in column A which if the code is written in a way to ignore the column A. Please!!!
Thank you!