검색 및 특정 조건에 따라 외부 통합 문서에서 값을 받고 Microsoft Excel에서 VBA를 사용하여 포맷 스타일과 함께 값을 붙여.

Option Explicit

Sub CallingProcedure()

'Calling procedure ReadFormatting for cell N13

Call ReadFormatting(Range("N13"))



End Sub

Sub ReadFormatting(rng As Range)

Dim varRow, varCol As Long

Application.ScreenUpdating = False

'Activating workbook "Data file.xlsx"

Workbooks("Data file.xlsx").Activate

'Checking for Errors

'If any runtime error occur then it will the pointer to end of the procedure

On Error GoTo Last

'Finding the column no after matching rng value in the first row of "Data file.xlsx" workbook

varRow = Application.Match(rng.Value, Rows(1), 0)

'Offset method is used for moving one cell in the previous column

'Finding the row no after matching value of cell in the first column of "Data file.xlsx" workbook

varCol = Application.Match(rng.Offset(0, -1).Value, Columns(1), 0)

'Using If statement for checking errors

'If error not found in varRow and varCol then execute below code

If Not IsError(varRow) And Not IsError(varCol) Then

'Copying value of cell where match of row and column intersect

Cells(varCol, varRow).Copy



'Pasting the format of copied cell

rng.Offset(0, 1).PasteSpecial xlPasteFormats



'Pasting the value of copied cell

rng.Offset(0, 1).PasteSpecial xlPasteValues



'Unselecting the previous copied data and clearing the cache

Application.CutCopyMode = False



End If

Application.ScreenUpdating = True

Last:

Workbooks("Searching_And_Getting_Data_From_Other_File_Along_With_Formatting.xlsm").Activate



End Sub