CODEWITHSUNDEEP
excelvbacell
baash
baash

Reputation: 17

Using command button to automate a different sheet

I am trying automate my excel workbook consists of 5 sheets . There is one summary page where I am including a command button which basically allows to upload a file and then fills up in specific sheets. The error I am getting is instead of filling up the cells in specified sheets its filling up cells in the summary page i.e the page with the button. This is the code I have written so far. The below is an example where I am trying to direct a file with extension SIG in sheet 3

Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd
   .AllowMultiSelect = True
.Filters.Clear
.Filters.Add "All files", "*.*"
.Filters.Add "ID Files", "*.ID", 1
.FilterIndex = 1
.Title = "Select SSI Identity File"
.InitialFileName = ""
  If .Show = -1 Then
  ReDim selectedPaths(.SelectedItems.Count)
    For I = 0 To .SelectedItems.Count - 1
        selectedPaths(I) = .SelectedItems(I + 1)
        Close #1

If Right$(selectedPaths(I), 3) = "SIG" Then
Sheets(3).Select
Open selectedPaths(I) For Input As #1

x = 4
        txt = Input(LOF(1), 1)

        With RegExp
       .Pattern = "\B/.*"
       .Global = True
       .IgnoreCase = False
        txt = .Replace(txt, "")
        End With


        With RegExp
       .Pattern = "S[0-9A-Z][0-9A-Z][0-9]+"
       .Global = True
    Set matches = .Execute(txt)



For Each Match In matches
        Debug.Print Match.Value
        If x Mod 30 = 0 Then
                x = x + 4
         End If
                Cells(x, 1) = Match
               x = x + 1
               Cells(x, 1) = Match
        If x Mod 30 <> 0 Then
                x = x + 1
        End If
    Next





 End With

 End If
 Next

  End If
 End With
 End Sub

Thanks for the help !

Upvotes: 0

Views: 47

Answers (1)

Buntes Lama
Buntes Lama

Reputation: 81

I see, you select the sheet first and then fill the cells. That is not the safest way.

Try using the full reference to the cell.

Your section

    For Each Match In matches
        Debug.Print Match.Value
        If x Mod 30 = 0 Then
                x = x + 4
         End If
                Cells(x, 1) = Match
               x = x + 1
               Cells(x, 1) = Match
        If x Mod 30 <> 0 Then
                x = x + 1
        End If
    Next

should look like this:

    For Each Match In matches
        Debug.Print Match.Value
        If x Mod 30 = 0 Then
                x = x + 4
         End If
               ThisWorkbook.Sheets(3).Cells(x, 1) = Match
               x = x + 1
               ThisWorkbook.Sheets(3).Cells(x, 1) = Match
        If x Mod 30 <> 0 Then
                x = x + 1
        End If
    Next

I hope this helps, I think it really is that simple.

Upvotes: 1

Related Questions