Code Problem

[mar050703]

I am trying to write some code whereby if a checkbox is true it will add the info to a stock sheet (which is fine), also if an item from the stock sheet is sold it marks it as yes and should later delete the item. (also fine), However the two do not seem to work together, if I need to add stock and remove stock.

I have the following code:

If ChkPEx.Value = True Then
'Sheets("Stock").Select
Range("A2").Select
Do
    If IsEmpty(ActiveCell) = False Then
    ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TxtPXReg
ActiveCell.Offset(0, 1) = TxtPXMk
ActiveCell.Offset(0, 2) = TxtPXAmt
ActiveCell.Offset(0, 3) = TxtTo
Else
GoTo continue
End If

Which works fine. Followed by this (which checks for the sold item):

Sheets("Stock").Select
Range("E1").Select
Do
    If ActiveCell.Value <> "Yes" Then
    ActiveCell.Offset(1, 0).Select
   End If
   
Loop Until ActiveCell.Value = "Yes"
    ActiveCell.Select
    Selection.EntireRow.Delete

but together the first section of code does not work after the line:

 ActiveCell.Value = TxtPXReg

Please can someone help?

The other vital code maybe:

 Private Sub CMBStk_Change()
    Sheets("Sheet1").Select
    Range("A35").Select
    ActiveCell.Value = CMBStk
    TxtFrm.Text = Sheets("Sheet1").Range("B35").Text
    TxtCost.Text = Sheets("Sheet1").Range("C35").Text
    
    Sheets("Stock").Select
    Range("A1").Select
Do
If ActiveCell.Value <> Sheets("Sheet1").Range("A35") Then
    ActiveCell.Offset(1, 0).Select
End If

Loop Until ActiveCell.Value = Sheets("Sheet1").Range("A35")
    ActiveCell.Offset(0, 4) = "Yes"
    Sheets("Sheet1").Select
    Range("A2").Select
End Sub

 

[mar050703]

Sorry I should point out that the all code runs when a command button is clicked. Except where specified, such as CMBStk Change

 

[SkipVought]

Hi,

Overusing the Select & Activate methods is a very poor coding practice that can result in slow performance and other problems.

Here's an example of what you could do...

If ChkPEx.Value Then
   With Sheets("Stock")
'The .Object syntax references the previous With, in this case Sheets("Stock")
      With .Cells(.Rows.Count, "A").End(xlUp). 'This references the last data row
'Now you can assign data to the NEXT row
         .Value = TxtPXReg
         .Offset(0, 1).Value = TxtPXMk
         .Offset(0, 2).Value = TxtPXAmt
         .Offset(0, 3).Value = TxtTo
      End With
   End With
End If

 

[mar050703]

Skip

Thanks for your coding. Perfect.

 

[SkipVought]

Oops, I did say the NEXT row...

'Now you can assign data to the NEXT row
         .Offset(1, 0).Value = TxtPXReg
         .Offset(1, 1).Value = TxtPXMk
         .Offset(1, 2).Value = TxtPXAmt
         .Offset(1, 3).Value = TxtTo

 

[mar050703]

Skip.

The first code you sent worked for my purposes. I needed to move along column anyway - so fine