Column and Row spacing?

  • Thread starter Thread starter gjfeng
  • Start date Start date
G

gjfeng

The code below shows:
ColA
Qty1
Qty2
Qty3

can I modify it to show
ColA -ColB-ColC
Qty1
------Qty2
-----------Qty3


Code:
'Sheets("nvT").Range("c" & Columns.Count).End(xlUp)
(2).Resize(, 1).Value = qty

The code below shows:
ColA-ColB-ColC
Qty1-Qty2-Qty3


can I modify it to show:
ColA -ColB-ColC
Qty1
------Qty2
-----------Qty3

Code:
'Worksheets("nvT").Range("IV7", Worksheets("nvT").Cells(7,
Columns.Count).End(xlToLeft))(3).Value = qty
 
Hi,

You can do this with a spreadsheet formula:
In cell B2 enter: =IF(ROW()=COLUMN(),$A2,"")
Copy the formula down as far as you need and then to the right as far as
necessary.
Then Copy and Paste Values to get rid of the formulas
Finally delete all the entries from A2 down in column A.
 
not that I do not want to use formulas...but since this is a dynamic
database...it'll may reach 10,000+ formulas!
The full code is here:

Code:
Private Sub CommandButton1_Click()

If Sheets("nvT").Range("b1").Value = "" Then
msgbox "The Date Is Empty!"

Else

Dim box1 As Integer
box1 = msgbox("Ok to Update?", 1 + vbInformation, "Clear Confirm")
If box1 = 1 Then

Dim intRow As Integer
intRow = 2

Do While Worksheets("db").Cells(intRow, 2).Value <> ""
If CStr(Worksheets("db").Cells(intRow, 1).Value) =
Sheets("nvT").Range("b1").Value Then

proID = Worksheets("db").Cells(intRow, 6).Value
pro = Worksheets("db").Cells(intRow, 7).Value
Worksheets("nvT").Range("IV5", Worksheets("nvT").Cells(5,
Columns.Count).End(xlToLeft))(3).Value = proID
Worksheets("nvT").Range("IV6", Worksheets("nvT").Cells(6,
Columns.Count).End(xlToLeft))(3).Value = pro
'Iv5 must match the number 5, IV6 must match the number 6,
they determine the row
'the number in (3), 3 leaves 1 empty space inbetween, 4 leaves
2 empty space

cusID = Worksheets("db").Cells(intRow, 5).Value
cus = Worksheets("db").Cells(intRow, 4).Value
Sheets("nvT").Range("a" & Columns.Count).End(xlUp)(2).Resize(,
2).Value = _
Array(cusID, cus)

qty = Worksheets("db").Cells(intRow, 8).Value
'Sheets("nvT").Range("c" & Columns.Count).End(xlUp)
(2).Resize(, 1).Value = qty
'Worksheets("nvT").Range("IV7", Worksheets("nvT").Cells(7,
Columns.Count).End(xlToLeft))(3).Value = qty
End If

intRow = intRow + 1
Loop

Else

End If 'End of Box1
End If 'End of check if empty IF statement

End Sub

Private Sub CommandButton2_Click()

Dim box1 As Integer
box1 = msgbox("Ok to Clear?", 1 + vbExclamation, "Clear Confirm")
If box1 = 1 Then
Worksheets("nvT").Cells.Value = ""
Range("a1").Value = "Order Date"
Range("a2").Value = "Delivery Date"
Range("a3").Value = "Posting Date"
Range("a4").Value = "Unit Price"
Range("a5").Value = "Product ID"
Range("a6").Value = "Product"
Range("c1").Formula = "=IF(B1="""","""",TEXT(WEEKDAY(B1),""ddd""))"

Else

End If


End Sub

Private Sub CommandButton3_Click()
Range("b1").Value = "=TODAY()"
Range("b2").Value = "=TODAY()"
Range("b3").Value = "=TODAY()"
End Sub
 
the link of the fiel is here: http://www.geocities.com/gjfeng/protoV2.xls

click on the nvT sheet, then click update command button.
Qty values are 3 bag, 2 bag, 11.5Pkt

3 bag is in the yellow box, I need 2 bad and 11.5 Pkt in the yellow
box.


That's really how I wanted it to be.....or is there a better way?
 
Back
Top