need to know data position

S

s7y

New member
Joined
Aug 19, 2014
Messages
24
Reaction score
0
Points
0
hello everybody,

In sheet1 I input the data in rows. I then select and copy a row (macro shortcut crtl+p) and go to sheet 2 where I paste the data (in column) (macro shortcut crtl+m). This all kinda work.

In sheet 2 each column (range where I paste the data) has a name. I need this name to be visible in sheet1.

Please see attached sample, where I have inserted (in sheet1) the desired result manually.

thanks in advance for your help
 

Attachments

  • Sample.xlsx
    345.8 KB · Views: 15
I'm working on this - could you paste the two macro codes you use to do the copying and the pasting?
 
Here you go:

Dim zWeight
Dim zULD
Dim zDest
Dim zSpecials
Dim zRow

Sub copyData()

zRow = ActiveCell.row 'e.g. 1

'**************************************************************
'DO NOT ALLOW HEADINGS ROW TO BE COPIED..
'**************************************************************
If zRow = 1 Then
Beep
Exit Sub
End If
'**************************************************************
'TEST IF ALREADY COPIED..
'**************************************************************
zMarker = Range("G" & zRow).Value

If zMarker = 1 Then
saywhat = "This pallet has already been inserted!"
saywhat = saywhat & vbCr & vbCr
'saywhat = saywhat & "Check column [G]"
saywhat = saywhat & vbCr & vbCr

boxtitle = "Copy and Transpose process"
btns = vbOKOnly + vbExclamation

answer = MsgBox(saywhat, btns, boxtitle)

Exit Sub
End If

'**************************************************************
'CONTINUE WITH COPY PROCESS..
'**************************************************************
zWeight = Range("a" & zRow).Value
zULD = Range("b" & zRow).Value
zDest = Range("c" & zRow).Value
zSpecials = Range("e" & zRow).Value
Sheets("Loadplan").Select

End Sub

Sub transposeData()

zSourceRow = zRow 'fetch saved value

zRow = ActiveCell.row 'e.g. 1

ActiveCell.Value = zULD
ActiveCell.Offset(1) = zWeight
ActiveCell.Offset(2) = zDest
ActiveCell.Offset(3) = zSpecials
 
1. Is there only an End Sub missing from the bottom of the last macro? Or is there more code?
2. What code module(s) are these macros in?

If you have put these macros in a standard code-module then try changing your transposeData macro to something along the lines of:
Code: 
Sub transposeData()
zSourceRow = zRow  'fetch saved value
zRow = ActiveCell.Row  'e.g. 1

ActiveCell.Value = zULD
ActiveCell.Offset(1) = zWeight
ActiveCell.Offset(2) = zDest
ActiveCell.Offset(3) = zSpecials

x = Array(4, 10, 16, 22, 31, 34, 40, 49)
diff = 10000000000#
For Each n In x
  If Abs(n - zRow) < diff Then
    diff = Abs(n - zRow)
    headerrow = n
  End If
Next n
If ActiveCell.Column = 21 And headerrow = 40 Then headerrow = 49
Debug.Print headerrow
Header = Cells(headerrow, ActiveCell.Column).Value
With Sheets("Sheet1")  'adjust this if it isn't named "Sheet1".
  '  .Activate
  .Cells(zSourceRow, "F") = Header
  '.Cells(zSourceRow, "G") = 1 ' perhaps include this line?
End With
End Sub
 
Hello there,

1. there is more code that I use to eliminate the first 3 letters of zULD. This because of lack of space... this is the rest of the code:

Selection.Replace What:="pmc", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="pag", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="paj", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="pla", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ake", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Sheets("ULD").Select

'PLACE MARKER IN SOURCE SHEET TO SAY TRANSPOSE HAS BEEN DONE..
Sheets("ULD").Range("G" & zSourceRow).Value = 1


End Sub

Where you see "place Marker.." etc... this is what I am trying to eliminate with this thread.

2. the code is in a Module
 
p45cal, can I go ahead and ask a second question on the same sheets?
 
Quite a lot of changes below, so don't try and make the changes to your own code, instead cut and paste the code to replace your code (you can keep your original code by commenting it all out).
Code: 
Dim zWeight
Dim zULD As String  'this has been changed!!!
Dim zDest
Dim zSpecials
Dim zRow

Sub copyData()
zRow = ActiveCell.Row  'e.g. 1
'**************************************************************
'DO NOT ALLOW HEADINGS ROW TO BE COPIED..
'**************************************************************
If zRow = 1 Then
  Beep
  Exit Sub
End If
'**************************************************************
'TEST IF ALREADY COPIED..
'**************************************************************
'zMarker = Range("G" & zRow).Value
'If zMarker = 1 Then
If Cells(zRow, "A").Interior.ThemeColor <> xlNone Then 'look for any shading in column A.
  saywhat = "This pallet has already been inserted!"
  saywhat = saywhat & vbCr & vbCr & vbCr & vbCr
  'saywhat = saywhat & "Check column [G]"
  boxtitle = "Copy and Transpose process"
  btns = vbOKOnly + vbExclamation
  answer = MsgBox(saywhat, btns, boxtitle)
  Exit Sub
End If
'**************************************************************
'CONTINUE WITH COPY PROCESS..
'**************************************************************
zWeight = Range("a" & zRow).Value
zULD = Range("b" & zRow).Value

'Now to shorten zULD:
'Either:
'zULD = Mid(zULD, 4) 'to remove the first three characters regardless of what they are.
'Or (to remove the 5 sets of characters if they are present):
zz = Array("PLA", "AKE", "PAG", "PMC", "PAJ")
For Each n In zz
  zULD = Replace(zULD, n, "", Compare:=vbTextCompare)
Next n

zDest = Range("c" & zRow).Value
zSpecials = Range("e" & zRow).Value
Sheets("Loadplan").Select
End Sub

Sub transposeData()
zSourceRow = zRow  'fetch saved value
zRow = ActiveCell.Row  'e.g. 1
With ActiveCell
  .NumberFormat = "@" 'to format the cell as Text to preserve leading zeroes.
  .Value = zULD
  .Offset(1) = zWeight
  .Offset(2) = zDest
  .Offset(3) = zSpecials
End With
x = Array(4, 10, 16, 22, 31, 34, 40, 49)
diff = 10000000000#
For Each n In x
  If Abs(n - zRow) < diff Then
    diff = Abs(n - zRow)
    HeaderRow = n
  End If
Next n
If ActiveCell.Column = 21 And HeaderRow = 40 Then HeaderRow = 49
Header = Cells(HeaderRow, ActiveCell.Column).Value
Range("A1").Select 'because activecell is used above, you can't do this until now.
With Sheets("ULD")  'adjust this if it isn't named "ULD".
  .Activate
  .Cells(zSourceRow, "F") = Header
  'PLACE MARKER IN SOURCE SHEET TO SAY TRANSPOSE HAS BEEN DONE..
  '.Cells(zSourceRow, "G") = 1
  'or remove the above line (and the conditional formatting in the sheet) and shade the cells with:
  With .Cells(zSourceRow, "A").Resize(, 5).Interior
    .ThemeColor = xlThemeColorDark2
    .TintAndShade = -9.99786370433668E-02
  End With
End With
End Sub

It does a few things (with alternatives in commented-out code).
1. It shortens the zULD during the copying process (rather than the pasting process).
2. There is an alternative commented-out snippet to remove the first 3 characters.
3. When zULD is added to the active cell, the format of that cell is converted to "Text" to preserve leading zeroes.
4. zULD is Dimmed as String in the code.
5. The copy process uses shading in the cell in column A to determine if it's already been copied, instead of a 1 in column G.
6. The pasting process also shades the first 5 columns in a given row on the ULD sheet and does not insert a 1 into column G. I'd advise removing conditional formatting from that sheet.



re:
s7y said:
2. the code is in a Module
Click to expand...
Yes, it has to be in a module, the question was what type of module: Standard code module, a sheet's code module, the Thisworkbook code module (or even a Class module!).



s7y said:
p45cal, can I go ahead and ask a second question on the same sheets?
Click to expand...
Sure.

It's bedtime here.
 
the code works... but... when I clear Loadplan by using the following code:

Sub ClearLoad()
'
' ClearLoad Macro
'
Sheets("Loadplan").Select
Range("B5:R8,B11:Q14,B17:S20,B23:U30,B35:U38,B41:H48,N41:S48,T45:T48,U40:U48").Select
Selection.ClearContents
Range("B13").Select
Sheets("ULD").Select
Range("f2:f42").Select
Selection.ClearContents
Range("A2").Select
End Sub

the shading in ULD remains and when I select the shaded uld the error message ("this pallet has already been inserted!") appears.
 
also: I have tried to block some cells by using Data Validation. For example in Loadplan if data is present in cell B6 then no data is allowed in cells B12, B18, B24 and B28. If I input the data manually this works. if I use the ctrl+m then the validation is not working. is there a problem with the fact that after pasting the data in loadplan the next step is that the code opens ULD?
 
I have edited the code to clear the loadplan as follows:

Sub ClearLoad()
'
' ClearLoad Macro
'
Sheets("Loadplan").Select
Range("B5:R8,B11:Q14,B17:S20,B23:U30,B35:U38,B41:H48,N41:S48,T45:T48,U40:U48").Select
Selection.ClearContents
Range("B13").Select
Sheets("ULD").Select
Range("f2:f42").Select
Selection.ClearContents
Range("A2").Select
ActiveWindow.SmallScroll Down:=21
Range("A2:F42").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=-54
Range("A2").Select

End Sub

and that solved the problem. now I can select the pallets.
 
That can be shortened to:
Code: 
Sub ClearLoad()
'
' ClearLoad Macro
'
Sheets("Loadplan").Select
Range("B5:R8,B11:Q14,B17:S20,B23:U30,B35:U38,B41:H48,N41:S48,T45:T48,U40:U48").ClearContents
Range("B13").Select 'you may not need this line at all.
Sheets("ULD").Select
Range("f2:f42").ClearContents
Range("A2").Select
Range("A2:E42").Interior.ThemeColor = xlNone
End Sub

Regarding validation to block cells; how have you set up the data validation?

A workbook sample would be very useful.
 
Pascal,

attached please find a sample. Check Sheet2!B6, that is where I have put the conditional formatting. the reason why I need the conditional formatting (or anything that can work, for that matter) is because what you see in sheet2 are the different configuration of the airplane. If Position A is taken, position AA, AB, ABR and ABR are not available anymore. I do have more positions that I have to close.

Also... can I use more conditional formatting for the same cell? I need to limit the max weight for each cell in the sheet.

Oh... before I forget... thanks for all you guys are doing to help me out. I really do appreciate it a lot.
 

Attachments

  • Sample cond form.xlsx
    347.5 KB · Views: 12
NBVC, my son of 3 loves your avatar... hammerhead shark...
 
It's my favourite shark... and my son (6) loves it too :)
 
s7y said:
Check Sheet2!B6, that is where I have put the conditional formatting. the reason why I need the conditional formatting (or anything that can work, for that matter) is because what you see in sheet2 are the different configuration of the airplane. If Position A is taken, position AA, AB, ABR and ABR are not available anymore. I do have more positions that I have to close.
Click to expand...
1. Are we talking Conditional Formatting or Data validation?
2. Whichever it is, when you're using a formula, it must equate to True or False; with Data Validation, False if it's invalid, True if it's valid. With Conditional formatting, True to get formatted and False not to get formatted.
3. Cell B6 is the only cell on the sheet with Data Validation; its formula:
if(or(B12>0,B18>0,B18>0,B24>0,B28>0)
is not a valid formula anyway. While developing such a formula, it's a good idea to put the formula in a cell on the same sheet somewhere to make sure that it's a valid formula and that it's returnng True/False. Once you have a successful formula, transfer it to the cell's Data Validation or Conditional Formatting and delete it from the cell you developed it in.
4. Data Validation is for validating data in the same cell, so B6 Data Validation would only control what goes in B6 and nowhere else.
5. Since you're using automation, it's probably easier to use Conditional Formatting throughout (you're right, Data Validation seems to be being ignored when a cell's value is changed by code). The sort of Conditional Formatting formula you could use in B6 to check that all of the cells B12, B18, B24 and B28 are empty is:
=COUNTA(B12,B18,B24,B28)>0
If they are all empty this will be False, if not it will be True.
Or better:
=AND(LEN(B6)>0,COUNTA(B12,B18,B24,B28)>0)
which will only highlight the cell if there's something in B6 AND there is something in one or more of the other 4 cells.

Because you can have multiple conditional formats in a cell, you can also shade cells such as B12, B18 etc. as soon as data is arrives in B6, to signal to the user that that space is not available; in B12 you could have conditional formatting formula of the ilk:
=COUNTA(B6)>0
This would not prevent data entry into B12 but if you were to add a further, different conditional format formula with different formatting to B12 such as:
=COUNTA(B6,B12)=2
with a format to alert the user there's an error, then the user can make a choice as to which cell needs to put right (B12 or B6) and the formatting will change accordingly. Of course, adjustments made here by the user will not appear on the other sheet! That's a whole new ball-game.
The conditional formatting on the sheet can become quite complex but with good planning could become very useful.


s7y said:
Also... can I use more conditional formatting for the same cell? I need to limit the max weight for each cell in the sheet.
Click to expand...
Yes, you can use many conditional formats for the same cell (as suggested above).
 
ok, got it...

but I need the cells to be absolutely unavailable when the weight or the positions don't allow for them to be used. Conditional formatting will highlight cells, text and so on but one is still allowed to put something in the cells while data validation will prevent this from happening.
 
s7y said:
but I need the cells to be absolutely unavailable when the weight or the positions don't allow for them to be used. Conditional formatting will highlight cells, text and so on but one is still allowed to put something in the cells while data validation will prevent this from happening.
Click to expand...
No, it doesn't look like it will;
p45cal said:
(you're right, Data Validation seems to be being ignored when a cell's value is changed by code)
Click to expand...
So it looks like Data Validation won't do it.
It'll require (a lot) more code to handle this - more than I'm prepared to do on a voluntary basis to help out here. The thing with absolutely preventing data entry when it would break the rules is that it leaves you no quick choice as to which thing you change (the data your trying to put in, or the data which is already there). If you want to change the data that's already there, you'll have to abort the current placing of data, erase the 'already there' data, then re-start both copy/paste actions.
 
You must log in or register to reply here.
Back
Top