Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Copy a cell value as text over to column B

Posted on 2013-05-31
11
Medium Priority
?
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

604 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