Solved Move a blue cell from one cell to another

Microsoft Outlook 2013 32/64-bit (mail m...
October 19, 2018 at 09:31:42
Specs: Windows 64
How can I move the first blue cell from one ws to another? I used conditional formatting to select certain codes that came in from a string so there should be one blue highlighted cell on each row. Whether it's in the C, D, E, F, or G, I need to move the first blue cell to the next worksheet.

Let me show you a couple lines:

A           B         C          D          E          F          G
Name       Sex        ST         ST         ST         ST         ST
J Doe       M         TX         MI         MN         AK         AL
W Roe       F         NE         CO         MI         MI         MA 

In this case, if I wanted to catch the first state code that started with M, MI, MA, MN would be blue.

Any ideas?


See More: Move a blue cell from one cell to another

Reply ↓  Report •

✔ Best Answer
October 19, 2018 at 12:59:27
re: "The reason I don't cut and paste this info is we're looking at 5000 rows at a time."

I was not suggesting that you use cut and paste. I was trying to get you explain what you meant by "move a cell". It now sounds like you don't want to move any cells, you want to copy a cell.

If I understand you correctly, you have a list of 8 codes that you need to search each Row for, starting in column C and ending in Column L. If one or more of those codes exist in that Row, you want the first code copied to Column M.

The following code should do that. You'll need to replace my Code1|Code2|Code3| with your actual codes. The bars | must remain as separators between your codes. No spaces either.

Sub FindFirstCode()

'Build Array of Codes
  Dim strCodeArray()    As String
  strCodeArray() = Split("Code1|Code2|Code3|Code4|Code5|Code6|Code7|Code8", "|", -1, vbBinaryCompare)
 
'Determine range of cells with data, based on Column A
  lastRw = Cells(Rows.Count, 1).End(xlUp).Row
   
'Loop through Rows
  For nxtRw = 2 To lastRw
    curCol = 13
    
'Loop through Code Array, find first cell with Code
   For nxtCode = 0 To 7
     With Range(Cells(nxtRw, 3), Cells(nxtRw, 12))
       Set c = .Find(strCodeArray(nxtCode), lookat:=xlWhole)
            If Not c Is Nothing Then
              If c.Column < curCol Then
                 curCol = c.Column
              End If
            End If
        End With
   Next nxtCode
        
'Place First Code or "No Code" in Column M
    If curCol = 13 Then
          Cells(nxtRw, 13) = "No Code"
    Else: Cells(nxtRw, 13) = Cells(nxtRw, curCol)
    End If
  Next nxtRw
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
October 19, 2018 at 09:59:21
Couldn't the same code you used to turn the cell blue be used to identify & move it?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
October 19, 2018 at 10:27:30
What do you mean by "move"? You can't actually move a cell.

You can move the contents via cut and paste and then you can delete the cell itself. That's sort of a "move", but not really. Please explain what you mean by "move".

As a far as finding the first blue cell, it seems to me that we really don't need to concern ourselves with the "blue". All we really care about is the contents of the cells. We just need to find the first cell that starts with M, beginning in Column C.

This code will do that:

Sub FindFirstM()
 lastRw = Cells(Rows.Count, 1).End(xlUp).Row
  For nxtRw = 2 To lastRw
   For nxtCol = 3 To Columns.Count
    If Left(Cells(nxtRw, nxtCol), 1) = "M" Then

       'Your "move" instructions would go here
       'For now, the code will display the cell address of
       'the first "M" in each Row, starting in Column C

        MsgBox Cells(nxtRw, nxtCol).Address
        Exit For

    End If
   Next
  Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#3
October 19, 2018 at 11:21:13
The reason I don't cut and paste this info is we're looking at 5000 rows at a time. After using conditional formatting to make them blue, I sorted by that color and moved the correct answers into another cell so each row has a 'blue' response in a single column so I could search that one column for a vlookup I perform in a separate workbook.This worked but it was very time consuming. I thought there had to be a simpler way..

And I tried to simpily my table in the demo but it's not the first that starts with M, it's 8 possible codes out of several hundred possibilities. I can't share the actual data but this is a better explanation: I get a report that has 5000+ customers we're reviewing. I get the customer name, number, a few other bits of information and then a single cell that includes the code for every possible review we'd have to run on the customer. (The question I asked last week about separating the codes when multiples were in one cell? The solution was to use the 'text to column' using the period as a separator.)

Now I have the customer name, number, a few other cells, then the cell with the long, unusable codes, and the cells I just created with an individual code type. My department only cares about 8 review codes so if there are 12 codes listed, I don't care, I don't need them all. If the customer on the first row has

A      B      C    D    E     F      G      H     I     J      K     L
Name Number  Code Code Code  Code   Code  Code   Code  Code   Code  Code
JDoe 123123  IT3  IT6  TE14 TE220A  TE20   IT7    IT6  TE220B
WRoe  4567   IT7  TE9  TE227  IT4    IT7   IT3   TE227 TE227  TE227  TE9

So if I look for IT3, IT6, TE224, and TE220A several of the cells above would be highlighted in blue but I just need one response, the first one. Some customers have only one code, others can have up to 20 but I just look at the first 10. If I don't get a blue cell, I have to look manually.

Ideally, I'd like to make column M my answer column so when I look for the review type for each customer, I only have to check one column.

Does this make sense?


Reply ↓  Report •

Related Solutions

#4
October 19, 2018 at 12:59:27
✔ Best Answer
re: "The reason I don't cut and paste this info is we're looking at 5000 rows at a time."

I was not suggesting that you use cut and paste. I was trying to get you explain what you meant by "move a cell". It now sounds like you don't want to move any cells, you want to copy a cell.

If I understand you correctly, you have a list of 8 codes that you need to search each Row for, starting in column C and ending in Column L. If one or more of those codes exist in that Row, you want the first code copied to Column M.

The following code should do that. You'll need to replace my Code1|Code2|Code3| with your actual codes. The bars | must remain as separators between your codes. No spaces either.

Sub FindFirstCode()

'Build Array of Codes
  Dim strCodeArray()    As String
  strCodeArray() = Split("Code1|Code2|Code3|Code4|Code5|Code6|Code7|Code8", "|", -1, vbBinaryCompare)
 
'Determine range of cells with data, based on Column A
  lastRw = Cells(Rows.Count, 1).End(xlUp).Row
   
'Loop through Rows
  For nxtRw = 2 To lastRw
    curCol = 13
    
'Loop through Code Array, find first cell with Code
   For nxtCode = 0 To 7
     With Range(Cells(nxtRw, 3), Cells(nxtRw, 12))
       Set c = .Find(strCodeArray(nxtCode), lookat:=xlWhole)
            If Not c Is Nothing Then
              If c.Column < curCol Then
                 curCol = c.Column
              End If
            End If
        End With
   Next nxtCode
        
'Place First Code or "No Code" in Column M
    If curCol = 13 Then
          Cells(nxtRw, 13) = "No Code"
    Else: Cells(nxtRw, 13) = Cells(nxtRw, curCol)
    End If
  Next nxtRw
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
October 19, 2018 at 13:54:01
That worked perfectly, DerbyDad, thank you much. I worship at the feet of your awesomeness! :-D

Reply ↓  Report •

#6
October 19, 2018 at 20:21:42

Reply ↓  Report •

Ask Question