View Full Version : [SOLVED:] Filling in blank cells with data from another workbook - VBA
CC268
01-17-2017, 01:50 PM
I have two different workbooks.
In Workbook 1 Column J contains Manufacturer Name. Some of the rows are filled in with the Manufacturer name while others are blank. I want to write VBA code that will go down each cell in Column J - if it is already filled in then don't worry about it, but if it is blank I want to take the Part Number in Column P and search for the corresponding Part Number in Workbook 2, Column A. If found copy the manufacturer in Column D and paste it back in Workbook 1 Column J.
Thanks!
mancubus
01-17-2017, 02:00 PM
upload your workbooks. replace confidential data where necessary.
(dont know how, see my signature...)
CC268
01-17-2017, 02:23 PM
upload your workbooks. replace confidential data where necessary.
(dont know how, see my signature...)
Here is sample data.1806018061
mancubus
01-17-2017, 03:18 PM
copy below code to a standard code module in WB 1, save it as xlsm, macro enabled workbook.
try it with backup files.
WB 2 must be open.
Sub vbax_58275_fill_blank_cells_from_another_workbook()
Dim FoundCell As Range
Dim LastRow As Long, i As Long
On Error Resume Next
With ThisWorkbook.Worksheets("Sheet1")
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
For i = 2 To LastRow
If .Cells(i, 10) = "" Then
Set FoundCell = Workbooks("Sample Workbook 2.xlsx").Worksheets("Sheet1").Columns(1).Find(.Cells(i, 16).Value)
If Not FoundCell Is Nothing Then
.Cells(i, 10).Value = FoundCell.Offset(, 3).Value
Else
.Cells(i, 10).Value = "Not found"
End If
End If
Next i
End With
End Sub
PS:
i assume part numbers in both workbooks are unique. otherwise Find method returns the first match.
you can remove below lines if you are sure there is always a match or if you want to leave cells blank when there is no match:
Else
.Cells(i, 10).Value = "Not found"
CC268
01-17-2017, 05:58 PM
Hmm...well it wrote Not found in all the blanks lol...but didn't do anything else
CC268
01-17-2017, 06:04 PM
It works! I had the Workbook name written with .xlsx instead of .xlsm lol. Thanks a ton! Beautiful code!
If you don't mind me asking what in the world does this line of code do:
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
mancubus
01-18-2017, 01:39 AM
you are welcome.
please mark the thread as solved from Thread Tools dropdown.
_________________________
quit excel
open a workbook with data.
make sure active cell in the active sheet with data is A1
turn macro recorder on
hit Ctrl + F key combination (or click Find & Select in Home Tab / Editing Group)
Find and Replace dialog pops up, "Find what" box containing an asterisk (*)
just click Find Next button
close the dialog
turn macro recorder off
now open VBE
recorded macro will show:
Sub Macro1()
'
' Macro1 Macro
''
Cells.Find(What:="*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub
now change xlNext to xlPrevious. and run the macro again. when you turn to excel you will see last non blank cell is selected.
Find Method Explained:
https://msdn.microsoft.com/en-us/library/office/ff839746.aspx
as you see this method has only one required parameter, which is "What".
so if you omit other optional parameters, default values are used. (Carefully read the Remarks in MS help page.)
and if you write the parameter values in function's/method's order, you don't need to write parameter names
so shorter version is:
Sub Macro1()
'
' Macro1 Macro
'
'
Cells.Find("*", , , , xlByRows, xlPrevious).Activate
End Sub
you can get this cell's row number, column number, address, etc by using related properties.
Sub FindLastNonBlankCellsProps()
Dim LastDataCell As Range
Dim LastRow As Long, LastCol As Long
Dim LastAddr As String
With ThisWorkbook.Worksheets("Sheet1")
Set LastDataCell = .Cells.Find("*", , , , xlByRows, xlPrevious)
End With
With LastDataCell
LastRow = .Row
LastCol = .Column
LastAddr = .Address
End With
MsgBox LastRow & vbLf & LastCol & vbLf & LastAddr
End Sub
or
Sub FindLastNonBlankCellsProps()
Dim LastRow As Long, LastCol As Long
Dim LastAddr As String
With ThisWorkbook.Worksheets("Sheet1")
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
LastCol = .Cells.Find("*", , , , xlByRows, xlPrevious).Column
LastAddr = .Cells.Find("*", , , , xlByRows, xlPrevious).Address
End With
MsgBox LastRow & vbLf & LastCol & vbLf & LastAddr
End Sub
CC268
01-18-2017, 09:04 AM
Thanks for the help I appreciate it!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.