VBA code insert value with specific text

Day

New Member
Joined
Nov 3, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
Hi guys, I need to insert the pending value with the table like this:
1636102887719.png

And insert value to this:
1636102949050.png

I have found this code:
VBA Code:
Sub Test1()

Dim row As Long, col As Long
Dim cell As Range, data As Range
Dim rangeFound As Range, rngResult As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

Set rngData = ws1.Range("B2", ws1.Cells(Rows.Count, "B").End(xlUp))

Sheets("Sheet1").Range("A3:C103").Copy Sheets("Sheet2").Range("A2")
Worksheets("Sheet2").Columns("A:BA").AutoFit

For Each cell In data
    Select Case cell
        Case ""
            Select Case True
                Case cell.Offset(0, -1) Like "Experiment *"
                    col = CLng(Trim(Split(cell.Offset(0, -1), "Experiment ")(1))) + cell.Column + 1
                    ws2.Cells(1, col) = cell.Offset(0, -1)
               
            End Select
        Case Else
            Set rngResult = ws2.Range("B2", ws2.Cells(Rows.Count, "B").End(xlUp))
            If rngResult.row = 1 Then Set rngResult = ws2.Range("B2")
            Set rangeFound = rngResult.Find(cell.Value, LookAt:=xlWhole)
            If rangeFound Is Nothing Then
                With ws1.Range("C1:C103")
                .Offset(, 5).Value = Evaluate("if(isnumber(match(" & .Address & ",{""Sym""Total esult",""},0)),""pending"","""")")
                End With
            Else
                With ws2.Cells(rangeFound.row, col)
                    .Value = cell.Offset(0, 2)
                End With
            End If
    End Select
Next

End Sub

The code works great inserting all the values but except the "Pending"

Thank's in advanced
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,798
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

It isn't as simple as copying all of column 4 to columns 5 and 6?
That is what it looks like from the example you posted.
 

Day

New Member
Joined
Nov 3, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
Welcome to the Board!

It isn't as simple as copying all of column 4 to columns 5 and 6?
That is what it looks like from the example you posted.
I'd like to do that, but there are still 50 cycles to go and copy and paste is too slow.o_Oo_O
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,798
Office Version
  1. 365
Platform
  1. Windows
I'd like to do that, but there are still 50 cycles to go and copy and paste is too slow.o_Oo_O
The whole copy/paste thing can be automated with VBA, but I am not sure what you mean by "50 cycles".
Perhaps you need to go into more detail about exactly what you are trying to do (you really didn't give us much detail in your original question).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,781
Messages
5,766,430
Members
425,353
Latest member
ExcelRalph

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top