Solved

Copy a cell value as text over to column B

Posted on 2013-05-31
11
267 Views
Last Modified: 2013-06-06
Looking for VBA that will on active sheet, check for red cell formatting in column AI and where ever it finds one, copy as text whatever is in that row over to column B and turn that one red as well, continuing down column AI until it does not find anymore.  There are blank cells in AI then a few red the a few more blanks and then more reds...

We are using Interior.ColorIndex = 3 for red.   -R-
0
Comment
Question by:RWayneH
  • 5
  • 5
11 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39212029
Hi,

Do you wish the copied cells into column [ B ] to be on the same row where they are found in column [AI], or a contiguous list of cells in column [ B ] not in-line with their original position (row) in column [AI]?

BFN,

fp.
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39212386
Please try this code:
Option Explicit

    Private Enum Nws                ' WorkSheet navigation
        NwsFirstDataRow = 2         ' change as required
        NwsTarget = 2               ' 2 defines column B
        NwsTest = 35                ' 35 defines column AI
    End Enum
    
Sub CopyRedCells()
    
    Dim R As Long
    
    With ActiveSheet
        For R = NwsFirstDataRow To .UsedRange.SpecialCells(xlCellTypeLastCell).Row
            With .Cells(R, NwsTest)
                If .Interior.ColorIndex = 3 Then
                    .Copy Destination:=.Cells(1, NwsTarget - NwsTest + 1)
                End If
            End With
        Next R
    End With
End Sub

Open in new window

Just paste all of it to a normal code module (by default 'Module1') in your workbook and save it as macro-enabled workbook with XLSM extension. Run the macro by placing the cursor within the procedure 'CopyRedCells' and press F5 while the sheet you wish to work on is active (last viewed). You can also call the procedure from the Developer tab -> Macros / select 'CopyRedCell' and press Run.
At the top of the code you have three enumerations which you can modify as required: The first data row (above that red cells will not be copied), the target and test columns (currently test column AI and write to column B).
Let me know if you need to adapt this to your requirement in greater detail.
0
 

Author Comment

by:RWayneH
ID: 39212841
Thanks I will test this.  To answer the question, yes if a cell in IA is formatted red, whatever is in that row needs to mirror in column B.  I did not see any reference to text.  The copied over data needs to be formatted as text. -R-
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39213396
Regarding your requirement to format the cell 's value in column B as Text, this may produce unexpected results! What is in the cell in column AI? Could it be a formula? If so, would you like the formula to be copied or its result? Could there be a number, either as cell content or cell value (result of a formula)? What is the cell format in column AI?
0
 

Author Comment

by:RWayneH
ID: 39216019
Code failing on Ln 15:   With .Cells(R, NwsTest)

AI is not a formula, the numbers in column AI are as follows:
084779601000010
044981301000000
077854102000010
084779901000010
016711804000000
068002302000000
043224701R6F158
013990301FT2693
081106301000000

I want to stay away from the E+ 07 type errors.  
In the past we have used things like:  .Range("AI1").Value = .Range("B1").Text

Hope this helps.  -R-
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 14

Expert Comment

by:Faustulus
ID: 39218069
Line 15 can only fail if you have set NwsFirstDataRow or NwsTest to a number smaller than 1. I have tested this code and it worked with me. Consider posting an excerpt from your workbook.
The numbers you are worried about do not pose a problem because my code copies the entire cell, not only its value. If the number displays correctly at its source it will display correctly at its destination.
0
 

Author Comment

by:RWayneH
ID: 39218740
Not sure Ln 1-8 are even being set, still failing for me.  I have not ever used code that you can set the column, row and other things in the Option Explicit?

Can we remove that and just use the needed values?  If edits are necessary I can just edit the code directly.

Sample file is attached.  Thanks -R-
CopyRedOverSample.xlsx
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39219699
Works like a charm without any alterations. I have installed the code in the attached workbook.
The variables being declared in as enumerations, separate and distinct from the code (not part of the Option statement), is much better programming practice than writing them into the code. It's rather like asking for the steering wheel in a car to be replaced with a handle bar because that is what you are used to from riding a bicycle. The exact point is that you have all the controls in one place and never need to look at the code.
The enum is now set correctly. If you don't touch it the macro will do its job perfectly.
EXX-130604-Copy-Red-Cells.xlsm
0
 

Author Comment

by:RWayneH
ID: 39221887
It appears to be error'ing because I am copying it to my Personal.xlsb macro file.  It works fine for me outside of the personal.xlsb.  This is something I will use off and on and will not be contained in a specific workbook.  How do I get this to work inside of the personal.xlsb file? or is there another reason why it is failing for me.

I agree that it is a better programming practice and adds flexibility, now it is understanding it's limitation of use and when it is appropreate to use a when not.

Please advise and thanks. -R-
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 39225216
VBA can't be imagined without the use of enums. Excel uses them extensively for its own purposes. All the variables like vbRed, vbyes, vbOK, xlLeft or xlNone and many, many more are enums.
However, the following version of the code may be more suitable for deployment through the Personal.xlsb workbook.
Sub CopyRedCells()
    
    Const NwsFirstDataRow As Long = 2         ' change as required
    Const NwsTarget As Long = 2               ' 2 defines column B
    Const NwsTest As Long = 35                ' 35 defines column AI
    
    Dim R As Long
    
    With ActiveSheet
        For R = NwsFirstDataRow To .UsedRange.SpecialCells(xlCellTypeLastCell).Row
            With .Cells(R, NwsTest)
                If .Interior.ColorIndex = 3 Then
                    .Copy Destination:=.Cells(1, NwsTarget - NwsTest + 1)
                End If
            End With
        Next R
    End With
End Sub

Open in new window

0
 

Author Closing Comment

by:RWayneH
ID: 39225418
EXCELent!  Thanks for the code adj, that was it and why it was failing.  It did not run from the personal.xlsb file that I put it in.  I believe it was assumed that it was going to be used in a workbook.  -R-
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now