MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.
Option Explicit Private Sub CommandButton1_Click() Dim FullFileName As String FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _ 1, "Custom Dialog Title", , False) Workbooks.Open FullFileName Dim wbOpen As Workbook Set wbOpen = Workbooks.Open(FullFileName) Dim xSheet As Worksheet xSheet = wbOpen.Worksheet 'wrong here Dim m As Integer Dim n As Integer m = xlCellTypeLastRow n = xlCellTypeLastCol With wbOpen Dim X As Long Dim Y As Long X = xlCellTypeLastRow Y = xlCellTypeLastCol Dim i As Integer Dim j As Integer Dim a As Integer Dim b As Integer For i = 1 To X Sheet2.Cells(i, 1) = xSheet(1).Cells(i, 1) Sheet2.Cells(i, 2) = xSheet(1).Cells(i, 2) Sheet2.Cells(i, 3) = xSheet(1).Cells(i, 3) For a = 1 To m If (Sheet1.Cells(a, 3) = xSheet.Cells(i, 1) And Sheet1.Cells(a, 4) = xSheet.Cells(i, 2) And Sheet1.Cells(a, 5) = xSheet.Cells(i, 3)) Then b = a Exit For End If Next a Sheet2.Cells(i, 4) = Sheet1.Cells(a, 1) Sheet2.Cells(i, 5) = Sheet1.Cells(a, 2) Next i End With End Sub Function xlCellTypeLastRow() Dim LastRow As Long With ActiveSheet LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row End With xlCellTypeLastRow = LastRow End Function Function xlCellTypeLastCol() Dim LastCol As Long With ActiveSheet LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With xlCellTypeLastCol = LastCol End Function
|How do you check for multiple possibilities in Excel||7||29|
|Excel export Active Sheet in one workbook to a new sheet in another||8||28|
|Excel: How to compare data in two columns||17||33|
|Calculate waiting times that spans two days||9||33|