Need a serous MACRO code

Microsoft Excel 2003 (full product)
August 31, 2010 at 07:58:56
Specs: Windows XP
A B C
1Internet.

2. 1-1-1

3. 1-1-1 Red

4. 1-1-1 White

5. 1-1-1 Blue

6.Total Internet


7..Phone


8. 1-1-1


9. 1-1-1 Miami

10. 1-1-1 Jersey


11. 1-1-1 Arizona


12. 1-1-1 Illinois


13.Total Phone


I need a serioous code to:

(1).For an empty cell in Column C between the rows with Internet and Total Internet In Column A to be filled with the word "Black".
(2) If those same rows do not have Black, White or Blue in Column C to change it to the word "Color". In my example "Red" would be changed to "Color" .
(3) I will apply a similar code to rows with Phone and Total Phone in Column A. I will jsut teak the code.


See More: Need a serous MACRO code

Report •


#1
September 1, 2010 at 08:44:42
re: I need a serioous code...

Everyone has needs.

Some people just tell us about them, others actually ask for assitance.

Which group are you in?


Report •

#2
September 2, 2010 at 08:04:26
I am defintely asking for assistance. Any help would be greatly appreciated.

Report •

#3
September 2, 2010 at 10:10:44
OK, so a question:

I can't tell by the example data given what values are in what column. You may have lined it up correctly when you entered it in your post, but unless you use the pre tags found above the reply box, you lose all the formatting.

For example, you want Black to be put in Column A but I can't tell if there is already something in Column A or not. I also can't tell what columns the words "Internet" and "Total Internet" are in, so it hard to write a macro that looks for those words.

By using the pre tags, you can make your data look like this:


         A            B           C 
1                  Internet 

2                   1-1-1 

3                   1-1-1         Red

4                   1-1-1         White

5                   1-1-1         Blue

6                   Total Internet


Use the pre tags, enter your data between them and click the Preview button. If you don't like the way it looks, edit the layout below the preview window, click the "Check To Show Confirmation Page Again" and click either Confirm button. This will allow you to preview and edit the post again. Repeat as required. Once you are happy with the way it looks, just click one of the confirm buttons.



Report •

Related Solutions

#4
September 2, 2010 at 18:57:15
Hi,

I assume - but please tell me if I am wrong - that "Internet" & "Total Internet" and "Phone" & "Total Phone" are in column A

In column C there are empty cells as well as colors.

To suggest a macro I have some questions:

1. Are the colors and substitutions the real values.
2. If you want to use the same code for other groups is there any logic to the values and the substitutions. Writing a macro to replace "Not Black, White or Blue" with "Color" is not going to work with other options.
3. You could re-write the macro for lots of options, but if you provide more detail it might be possible to offer a macro that uses a lookup table that allows multiple groups to be handled by just creating / editing a table, as opposed to re-writing sections of code. Please provide the full details of the data and it's organization.
4.Please confirm that "1-1-1" is (a) all in one cell, (b) that it is not real data and (c) that it has no impact on any macro.

Regards


Report •

#5
September 8, 2010 at 07:05:07
Hello Humar,

In answer to your questions:
(1) the color and substitutions are not the real values
(2) i need a code to use as a template for multiple situations similar to my example below. I will simply change the values to fit the desired code.
(3) i am a novice at this, so i do not know what you man when you say lookup table and other terms.
(4)i hope my example below better explains the layout. It is not real data, just an example.

Thank you for your help in this matter.


A ------------------B-------------C
Internet------

-------------------1/1/10------

-------------------1/1/10-------Red

-------------------1/1/10------White

--------------------1/1/10-------Blue

Total Internet


Phone


---------------------1/1/10------


---------------------1/1/10-------Miami

---------------------1/1/10-------Jersey


---------------------1/1/10-------Arizona


---------------------1/1/10------Illinois


Total Phone


I need a serioous code to:

(1).For an empty cell in Column C between the rows with Internet and Total Internet In Column A to be filled with the word "Black".
(2) If those same rows do not have Black, White or Blue in Column C to change it to the word "Color". In my example "Red" would be changed to "Color" .
(3) I will apply a similar code to rows with Phone and Total Phone in Column A. I will justt teak the code


Report •

#6
September 10, 2010 at 06:52:11
Humar & DerbyDad03,

Thank you both your interest in this matter. I appreciate the help both of you have already provided. Cheers.

Respectfully,
Nicky B


Report •

#7
September 10, 2010 at 11:21:50
Humar & DerbyDad03,

Thank you both your interest in this matter. I appreciate the help both of you have already provided. Cheers.

Respectfully,
Nicky B


Report •

#8
September 11, 2010 at 06:44:29
Hi,

As I mentioned before, a table lookup approach would be appropriate.

You build a table containing the text that starts and ends each block and you include the three acceptable values and you have the replacement values for an empty cell in column C and a cell in column C that contains a value, but the value is not one of the three acceptable values.

I built the table starting with its heading in cell G1, like this:

	G		H	I	J	K	L	M
1	Type replacement table						
2	Start text	End Text	Empty	Not	Allow 1	Allow 2	Allow 3
3	Internet	Total Internet	White	Color	Black	White	Blue
4	Phone		Total Phone	Miami	Tampa	Arizona	Jersey	Illinois

The table can be extended as required.
The start of the data in the table at cell G3 is hard-coded into the macro, and can be changed at this line:
    'set start of lookup table
    Set rngTableStart = .Range("G3")

Here is the macro:

Option Explicit

Sub FillReplace()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngData As Range
Dim rngCell As Range
Dim rngTableStart As Range
Dim rngTableEnd As Range
Dim rngTable As Range
Dim rngFind As Range
Dim blnInData As Boolean

With Worksheets("Sheet1")
    'set start of data in column A
    Set rngStart = .Range("A2")
    'find end of data in column A
    Set rngEnd = .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp)
    'set Range for first column of Data
    Set rngData = Range(rngStart, rngEnd)
    'set start of lookup table
    Set rngTableStart = .Range("G3")
    'find end of table
    Set rngTableEnd = .Cells(Application.Rows.Count, rngTableStart.Column) _
                    .End(xlUp)
    'set Range for first column of Table
    Set rngTable = Range(rngTableStart, rngTableEnd)
    'set 'InData' flag to false
    blnInData = False
    'loop through source data - first column
    For Each rngCell In rngData
        'test if in a data block
        If blnInData = True Then
            'test if end of data
            If rngCell.Text = rngFind.Offset(0, 1).Text Then
                'its the end of data block
                'so set InData flag to false
                blnInData = False
                Else
                'test column C
                Select Case rngCell.Offset(0, 2).Value
                    Case ""
                        'its empty so use replacement from table
                        '"Empty" column -> offset =2
                        rngCell.Offset(0, 2).Value = rngFind.Offset(0, 2).Value
                    Case rngFind.Offset(0, 4).Text
                        'its the first 'Allow' value
                        'do nothing
                    Case rngFind.Offset(0, 5).Text
                        'its the second 'Allow' value
                        'do nothing
                    Case rngFind.Offset(0, 6).Text
                        'its the third 'Allow' value
                        'do nothing
                    Case Else
                        'its not empty & its not an allowed value
                        'so replace with 'Not' value -> offset=3
                        rngCell.Offset(0, 2).Value = rngFind.Offset(0, 3)
                End Select
            End If
            Else
            'see if we have a start of data label
            Set rngFind = rngTable.Find(What:=rngCell.Text)
            If Not rngFind Is Nothing Then
                'found - so set InData flag
                blnInData = True
            End If
        End If
    Next rngCell
End With
End Sub

As changes made by macros cannot be undone with the undo function/button, test this on copies of your data. The macro has been tested only with your 'not real' data, and has not been tested with your data or in your environment, so test it to make sure that it works as expected, and always make a backup before running it on real data.

This:

Start/End	Data	Type
Internet		
		01/Jan/10	
		01/Jan/10	Red
		01/Jan/10	White
		01/Jan/10	Blue
Total Internet		

became:
Start/End	Data	Type
Internet		
		01/Jan/10	White
		01/Jan/10	Color
		01/Jan/10	White
		01/Jan/10	Blue
Total Internet		

... when the macro was run.

Regards


Report •

#9
September 13, 2010 at 12:52:51
Humar,

Will I have to insert the table with a macro?


Report •

#10
September 13, 2010 at 16:38:05
Hi,

The macro uses the table to make decisions about which block it is in e.g. Internet - Total Internet and in each block, what replacements to make.

If you look at the macro you will see code that relates to the table, for example:

    'set start of lookup table
    Set rngTableStart = .Range("G3")

Regards


Report •

#11
September 14, 2010 at 08:12:33
Humar,

Sorry, but i think I am a little confused. Were do I put that table?

Respectfully,
Nick


Report •

#12
September 14, 2010 at 10:51:31
Hi,

You never said much about what your real data was, or where it was, so I can only assume that you are dealing with a single worksheet with data in columns A, B & C

So, as I said in response #8, build a table starting at cell G1 - on the same worksheet as your data.

As the table I described used two rows for headers, the real data starts at cell G3 - which is what is referenced in the macro.

When you get it to work, 'as described' you could then choose to place the table somewhere else and edit the address in the macro.

Regards


Report •

#13
September 14, 2010 at 13:00:21
Humar,
Can I send you a sample file, the macro I am using to modify it, and the table I created per your suugestion. I still need to modify my macro to include a macro using the table

Respectfully,
Nick


Report •

#14
September 14, 2010 at 14:14:27
Hi,

Can you post the macro.

Regards


Report •

#15
September 16, 2010 at 08:55:56
Humar,

Below is the macro i am currently using. As you can see it does a lot for me. I would love your help. I think we may run into an issue as to where we have to include the part we are currrently working on. Plus, i was going to give you a sample of the actual data I need converted.

Respectfully,

Nick


Report •

#16
September 16, 2010 at 10:22:40
HUMAR,

Do you need the table I created?

Nick


Report •

#17
September 17, 2010 at 06:34:20
Hi,

OK,

Post the table.

Put your data between <pre> and </pre> tags that you can insert using the 'Pre' icon above the reply box. Then use the Preview button and edit, as required. To preview again, check the 'Check To Show Confirmation Page Again' box and click 'Confirm and see post'

Regards


Report •

#18
September 17, 2010 at 06:55:16

Thank you.

Report •

#19
September 17, 2010 at 07:00:38
Humar,
The whole replacement table did not go through.

Report •


Ask Question