0

I have a list where VBA needs to find the date in spreadsheets and also the machine name, then paste the UserForm text box. UserForm works, but it does not paste any info. I've played around with this for a few days, and can't figure out how to make it work.

Main sheet:

main sheet
(Click images to enlarge)

Result of VBA run:

result of VBA run

If someone need to download the complete file, the working file is located on the vbaexpress forum.

  • Shouldn't TextBox1 = .Cells... be the other way around? .Cells... = TextBox1. Though it would help testing it much faster if you could host and share the file. Just google "upload files" for a bunch of options. – Christofer Weber Mar 17 '19 at 19:33
  • ok i wil upload it to my server, just a moment thanks – martinrusten Mar 17 '19 at 20:09
  • rushfiles.one/client/publiclink.aspx?id=VZEITMAxCi select the arrow on the right to download – martinrusten Mar 17 '19 at 20:10
0

Seems like you figured out the important bits. I did some minor changes, like also copying the issue, and putting the error messages in another part of the if

Private Sub CommandButton1_Click()
Dim arr, Dat As Date, Mon$, rng As Range, mch As Range, mch1$, clm&, issue$
arr = Array("Januar", "Februar", "Mars", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Desember")
Dat = Sheets("Dayplan").Cells(r, 3)
mch1 = Sheets("Dayplan").Cells(r, 4)
issue = Sheets("Dayplan").Cells(r, 5)
Mon = arr(Month(Dat) - 1)

With Sheets(Mon)
    Set rng = .Rows(3).Find(Dat, lookat:=xlWhole)
    If Not rng Is Nothing Then
        Set mch = .Columns(1).Find(mch1, lookat:=xlWhole)
        If Not mch Is Nothing Then
            .Cells(mch.Row, rng.Column) = issue
            .Cells(mch.Row, rng.Column + 1) = TextBox1
            .Cells(mch.Row, rng.Column + 2) = Format(TextBox2, "dd-mm-yyyy")
            .Cells(mch.Row, rng.Column + 3) = TextBox3
        Else
            MsgBox "can not find machine"
        End If
    Else
        MsgBox "can not find date"
    End If
End With
Me.Hide

End Sub

or, without the nested IFs

With Sheets(Mon)
    Set rng = .Rows(3).Find(Dat, lookat:=xlWhole)
    Set mch = .Columns(1).Find(mch1, lookat:=xlWhole)
    If rng Is Nothing Then
        MsgBox "can not find date"
    ElseIf mch Is Nothing Then
        MsgBox "can not find machine"
    Else
        .Cells(mch.Row, rng.Column) = issue
        .Cells(mch.Row, rng.Column + 1) = TextBox1
        .Cells(mch.Row, rng.Column + 2) = Format(TextBox2, "dd-mm-yyyy")
        .Cells(mch.Row, rng.Column + 3) = TextBox3
    End If
End With
| improve this answer | |
  • without the nested IFs do not work, just get the error message, maybe its bechause the if not is inverted if i understand correctly. – martinrusten Mar 17 '19 at 22:18
  • im just totally noob on this, making a mess, frankenstein pices of codes from google and a looot of trial and error – martinrusten Mar 17 '19 at 22:19
  • the first also do not work, just return "date not found" – martinrusten Mar 17 '19 at 22:21
  • @martinrusten make sure you actually have a date in the Dayplan sheet, because it works for me. You can put a msgbox Dat in there just to see what you are searching for.. – Christofer Weber Mar 17 '19 at 22:45
0

BIG thanks to Christofer Weber

  .Cells(mch.Row, rng.Column + 1) = TextBox1
  .Cells(mch.Row, rng.Column + 2) = TextBox2 = Format("dd-mm-yyyy")
  .Cells(mch.Row, rng.Column + 3) = TextBox3

this works but i get only UNTRUE / NOT TRUE for textbox2 (not sure what the englich excel say)

my file is here: avalible in 14 days

rushfiles.one/client/publiclink.aspx?id=VZEITMAxCi

select the arrow on the right to download

format do not work, how to fix it?

edit, made this work:

  .Cells(mch.Row, rng.Column + 1) = TextBox1
   TextBox2.Value = Format(TextBox2.Value, "dd/mm/yyyy")
  .Cells(mch.Row, rng.Column + 2) = TextBox2
  .Cells(mch.Row, rng.Column + 3) = TextBox3
| improve this answer | |
  • .Cells(mch.Row, rng.Column + 2) = Format(TextBox2, "d/m/yy") Or whatever format you like. – Christofer Weber Mar 17 '19 at 21:28
0

have no clue why but when i copy your code it do not work, when i copy only part of your code and paste it in to mine it works.

anyway big thanks again, all works fine now, im so happy after 3 days of googeling and posts on 12 different forums

here is the code that works:

Private Sub CommandButton1_Click()
Dim arr, Dat As Date, Mon$, rng As Range, mch As Range, mch1$, clm&
arr = Array("Januar", "Februar", "Mars", "April", "Mai", "Juni", "Juli", "August", 
"September", "Oktober", "November", "Desember")
Dat = Sheets("Dayplan").Cells(r, 3)
mch1 = Sheets("Dayplan").Cells(r, 4)
Mon = arr(Month(Dat) - 1)

With Sheets(Mon)
  Set rng = .Rows(3).Find(Dat, lookat:=xlWhole)
  If Not rng Is Nothing Then
    Set mch = .Columns(1).Find(mch1, lookat:=xlWhole)
    If Not mch Is Nothing Then

      .Cells(mch.Row, rng.Column + 1) = TextBox1
       TextBox2.Value = Format(TextBox2.Value, "dd/mm/yyyy")
      .Cells(mch.Row, rng.Column + 2) = TextBox2
      .Cells(mch.Row, rng.Column + 3) = TextBox3

        Else
            MsgBox "can not find machine"
    End If
    Else
        MsgBox "can not find date"
  End If
End With
Me.Hide
End Sub
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.