none
Excel VBA write data to worksheet

    Question

  • Hi

    Hi

    I am using excel 2007. I am trying to write data from a user form on the Command button named Cmdsave to worksheet("Sheet1). The code below executes this. I also want the data to write to a second sheet worksheet("sheet2") if a value in userform  textbox "txtunitA" = B (the letters A,B,C,D can be selected by person inputting data on the Userform) I hope I have explained the problem.

    thanks

    Gerry

    Private Sub CmdsaveA_Click()

    ' Write data to Worksheet
    RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Sheet1").Range("A1")
    .Offset(RowCount, 0).Value = Me.Comdate.Value
    .Offset(RowCount, 1).Value = Me.ComsurnameA.Value
    .Offset(RowCount, 2).Value = Me.TxtforeA.Value
    .Offset(RowCount, 3).Value = Me.TxtregA.Value
    .Offset(RowCount, 4).Value = Me.TxtunitA.Value

    ' Write data to Worksheet if value in "txtUnitA" = B

    ?????????????
    RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Sheet2").Range("A1")
    .Offset(RowCount, 0).Value = Me.Comdate.Value
    .Offset(RowCount, 1).Value = Me.ComsurnameA.Value
    .Offset(RowCount, 2).Value = Me.TxtforeA.Value
    .Offset(RowCount, 3).Value = Me.TxtregA.Value
    .Offset(RowCount, 4).Value = Me.TxtunitA.Value

     

    Wednesday, November 09, 2011 12:24 PM

Answers

  • Gerry

    May be I'm missing something, but I think you just have to add an IF statement.

    ...

    if Me.TxtunitA.Value = "B" Then

    RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Sheet2").Range("A1")
    .Offset(RowCount, 0).Value = Me.Comdate.Value
    .Offset(RowCount, 1).Value = Me.ComsurnameA.Value
    .Offset(RowCount, 2).Value = Me.TxtforeA.Value
    .Offset(RowCount, 3).Value = Me.TxtregA.Value
    .Offset(RowCount, 4).Value = Me.TxtunitA.Value

    End If


    Eddy N.
    • Marked as answer by bigger312 Wednesday, November 09, 2011 2:01 PM
    Wednesday, November 09, 2011 1:36 PM

All replies

  • Gerry

    May be I'm missing something, but I think you just have to add an IF statement.

    ...

    if Me.TxtunitA.Value = "B" Then

    RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Sheet2").Range("A1")
    .Offset(RowCount, 0).Value = Me.Comdate.Value
    .Offset(RowCount, 1).Value = Me.ComsurnameA.Value
    .Offset(RowCount, 2).Value = Me.TxtforeA.Value
    .Offset(RowCount, 3).Value = Me.TxtregA.Value
    .Offset(RowCount, 4).Value = Me.TxtunitA.Value

    End If


    Eddy N.
    • Marked as answer by bigger312 Wednesday, November 09, 2011 2:01 PM
    Wednesday, November 09, 2011 1:36 PM
  • Thanks

    I am new to Excel VBA and I am starting to get my head around it.

    I was trying to make it complicated

    Thanks for taking the time to help me out.

     

    Gerry

    Wednesday, November 09, 2011 2:01 PM