All,
I have a VB Script that is functioning properly but has two minor issues that I can't seem to fix.
Script Purpose: The script compares two worksheets. It uses comma separated entries in the 'CTP' sheet and attempts to find a corresponding match (via the Split and Find commands) in the 'SRTM' sheet. If a match is found, it copies the corresponding entry from Column A of the 'CTP' sheet to Column B of the 'SRTM' sheet.
Issue #1: It copies only the first split for each cell properly. It does not work for any of the subsequent splits in the For Loop, even though each split (array element) has a valid value and should be findable in the spreadsheet.
Issue #2: There are some cases where multiple matches occur and the script needs to be able to append a value to the cell rather than overwrite the value. This issue is with the following line: Sheets("SRTM").Cells(c4.Row, 2) = c3.Offset(, -1)
The script that needs to be reworked is below. The sample worksheets are attached as well.
Thanks in advance.
![]()
Sub copyfrom1() Dim c3 As Range, c4 As Range, LR2 As String, ms As Worksheet, LR1 As String, x Application.ScreenUpdating = 0 Application.EnableEvents = 0 With Sheets("CTP") LR2 = Worksheets("CTP").Cells(Rows.Count, 1).End(xlUp).Row LR1 = Worksheets("SRTM").Cells(Rows.Count, 1).End(xlUp).Row For Each c3 In .Range("B2:B" & LR2) If Len(c3.Value) Then x = Split(c3.Value, ",") For i = 0 To UBound(x) Set c4 = Sheets("SRTM").Range("A2:A" & LR1).Find(x(i), , xlValues, xlPart) If Not c4 Is Nothing Then Sheets("SRTM").Cells(c4.Row, 2) = c3.Offset(, -1) End If Next End If Next End With Application.ScreenUpdating = 1 Application.EnableEvents = 1 End Sub











LinkBack URL
About LinkBacks
Register To Reply

Bookmarks