| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Cell Text/Data
Hi,
What I am doing at the mo is this:- I have a spread sheet which is used each month for reporting applications, the data is extracted from a database and contains some duplicates. I need to check theses duplicates against each other and ensure their result fields meet certain criteria. If they Do/Dont then I need to change/Not Change the data in the cells. For Instance, Col B contains the application numbers, col F Contains the first part of the test, say it contains the word 'Refused' Col G the next part say 'Overruled' and Col H the Last part Say 'Unknown' Now if the next row down contains the same application Number but the subsequent rows contain the words 'Refused'-'Included' and then 'Unknown' I need to change the Unkinowns to a different result say 'Mickey Mouse' I can set up the code in VBA to check the 3 cells and change the last one depending on the criteria of the other 2 on the spreadsheet as a whole, I can write the formula on the spreadsheet to check col B for duplicates and return a word or ref, However what I cant do is marry the 2 together so that the only cells changed are those appertaining to the duplicate entries. In essence what I need is a bit of help getting the code to check Col B for duplicates then do the offset stuff on only those duplicate entries Cheers JR |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Cell Text/Data
It's possible.
I'd suggest something like this... ----------------- dim intCounter as integer intCounter = 2 Do while len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).text = Range("B" & intCounter - 1).Text then {Do the cell alterations you want here} End If intCounter = intCounter + 1 Loop --------------------- All you need to do is use intCounter as your range reference and you should be OK. HTH. "John R" wrote: Hi, What I am doing at the mo is this:- I have a spread sheet which is used each month for reporting applications, the data is extracted from a database and contains some duplicates. I need to check theses duplicates against each other and ensure their result fields meet certain criteria. If they Do/Dont then I need to change/Not Change the data in the cells. For Instance, Col B contains the application numbers, col F Contains the first part of the test, say it contains the word 'Refused' Col G the next part say 'Overruled' and Col H the Last part Say 'Unknown' Now if the next row down contains the same application Number but the subsequent rows contain the words 'Refused'-'Included' and then 'Unknown' I need to change the Unkinowns to a different result say 'Mickey Mouse' I can set up the code in VBA to check the 3 cells and change the last one depending on the criteria of the other 2 on the spreadsheet as a whole, I can write the formula on the spreadsheet to check col B for duplicates and return a word or ref, However what I cant do is marry the 2 together so that the only cells changed are those appertaining to the duplicate entries. In essence what I need is a bit of help getting the code to check Col B for duplicates then do the offset stuff on only those duplicate entries Cheers JR |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Cell Text/Data
"DaveO"
Used your suggestion but unfortunately it still isn't playing, this is what I have input, Dim intCounter As Integer intCounter = 2 Do While Len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).Text = Range("B" & intCounter - 1).Text Then 'Do the cell alterations you want here If ActiveCell.Offset(0, 4).Value = "Refused" Then If ActiveCell.Offset(0, 5).Value = "Overruled" Then If ActiveCell.Offset(0, 6).Value = "REFNOAA" Then If ActiveCell.Offset(1, 4).Value = "Refused" Then If ActiveCell.Offset(1, 5).Value = "Included" Then If ActiveCell.Offset(1, 6).Value = "REFWITHAA" Then ActiveCell.Offset(0, 6).Value = "Partial" ActiveCell.Offset(1, 6).Value = "Partial" End If End If End If End If End If End If End If intCounter = intCounter + 1 can you (or anyone else) see where I'm going wrong Cheers John R "DaveO" wrote: It's possible. I'd suggest something like this... ----------------- dim intCounter as integer intCounter = 2 Do while len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).text = Range("B" & intCounter - 1).Text then {Do the cell alterations you want here} End If intCounter = intCounter + 1 Loop --------------------- All you need to do is use intCounter as your range reference and you should be OK. HTH. "John R" wrote: Hi, What I am doing at the mo is this:- I have a spread sheet which is used each month for reporting applications, the data is extracted from a database and contains some duplicates. I need to check theses duplicates against each other and ensure their result fields meet certain criteria. If they Do/Dont then I need to change/Not Change the data in the cells. For Instance, Col B contains the application numbers, col F Contains the first part of the test, say it contains the word 'Refused' Col G the next part say 'Overruled' and Col H the Last part Say 'Unknown' Now if the next row down contains the same application Number but the subsequent rows contain the words 'Refused'-'Included' and then 'Unknown' I need to change the Unkinowns to a different result say 'Mickey Mouse' I can set up the code in VBA to check the 3 cells and change the last one depending on the criteria of the other 2 on the spreadsheet as a whole, I can write the formula on the spreadsheet to check col B for duplicates and return a word or ref, However what I cant do is marry the 2 together so that the only cells changed are those appertaining to the duplicate entries. In essence what I need is a bit of help getting the code to check Col B for duplicates then do the offset stuff on only those duplicate entries Cheers JR |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Cell Text/Data
So the If statements all have to be true for anything to happen, is that
correct? Also, I'm assuming the Loop at the end was omitted when you copied it over? "John R" wrote: "DaveO" Used your suggestion but unfortunately it still isn't playing, this is what I have input, Dim intCounter As Integer intCounter = 2 Do While Len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).Text = Range("B" & intCounter - 1).Text Then 'Do the cell alterations you want here If ActiveCell.Offset(0, 4).Value = "Refused" Then If ActiveCell.Offset(0, 5).Value = "Overruled" Then If ActiveCell.Offset(0, 6).Value = "REFNOAA" Then If ActiveCell.Offset(1, 4).Value = "Refused" Then If ActiveCell.Offset(1, 5).Value = "Included" Then If ActiveCell.Offset(1, 6).Value = "REFWITHAA" Then ActiveCell.Offset(0, 6).Value = "Partial" ActiveCell.Offset(1, 6).Value = "Partial" End If End If End If End If End If End If End If intCounter = intCounter + 1 can you (or anyone else) see where I'm going wrong Cheers John R "DaveO" wrote: It's possible. I'd suggest something like this... ----------------- dim intCounter as integer intCounter = 2 Do while len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).text = Range("B" & intCounter - 1).Text then {Do the cell alterations you want here} End If intCounter = intCounter + 1 Loop --------------------- All you need to do is use intCounter as your range reference and you should be OK. HTH. "John R" wrote: Hi, What I am doing at the mo is this:- I have a spread sheet which is used each month for reporting applications, the data is extracted from a database and contains some duplicates. I need to check theses duplicates against each other and ensure their result fields meet certain criteria. If they Do/Dont then I need to change/Not Change the data in the cells. For Instance, Col B contains the application numbers, col F Contains the first part of the test, say it contains the word 'Refused' Col G the next part say 'Overruled' and Col H the Last part Say 'Unknown' Now if the next row down contains the same application Number but the subsequent rows contain the words 'Refused'-'Included' and then 'Unknown' I need to change the Unkinowns to a different result say 'Mickey Mouse' I can set up the code in VBA to check the 3 cells and change the last one depending on the criteria of the other 2 on the spreadsheet as a whole, I can write the formula on the spreadsheet to check col B for duplicates and return a word or ref, However what I cant do is marry the 2 together so that the only cells changed are those appertaining to the duplicate entries. In essence what I need is a bit of help getting the code to check Col B for duplicates then do the offset stuff on only those duplicate entries Cheers JR |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Cell Text/Data
Thats Correct on both counts,
John R "DaveO" wrote: So the If statements all have to be true for anything to happen, is that correct? Also, I'm assuming the Loop at the end was omitted when you copied it over? "John R" wrote: "DaveO" Used your suggestion but unfortunately it still isn't playing, this is what I have input, Dim intCounter As Integer intCounter = 2 Do While Len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).Text = Range("B" & intCounter - 1).Text Then 'Do the cell alterations you want here If ActiveCell.Offset(0, 4).Value = "Refused" Then If ActiveCell.Offset(0, 5).Value = "Overruled" Then If ActiveCell.Offset(0, 6).Value = "REFNOAA" Then If ActiveCell.Offset(1, 4).Value = "Refused" Then If ActiveCell.Offset(1, 5).Value = "Included" Then If ActiveCell.Offset(1, 6).Value = "REFWITHAA" Then ActiveCell.Offset(0, 6).Value = "Partial" ActiveCell.Offset(1, 6).Value = "Partial" End If End If End If End If End If End If End If intCounter = intCounter + 1 can you (or anyone else) see where I'm going wrong Cheers John R "DaveO" wrote: It's possible. I'd suggest something like this... ----------------- dim intCounter as integer intCounter = 2 Do while len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).text = Range("B" & intCounter - 1).Text then {Do the cell alterations you want here} End If intCounter = intCounter + 1 Loop --------------------- All you need to do is use intCounter as your range reference and you should be OK. HTH. "John R" wrote: Hi, What I am doing at the mo is this:- I have a spread sheet which is used each month for reporting applications, the data is extracted from a database and contains some duplicates. I need to check theses duplicates against each other and ensure their result fields meet certain criteria. If they Do/Dont then I need to change/Not Change the data in the cells. For Instance, Col B contains the application numbers, col F Contains the first part of the test, say it contains the word 'Refused' Col G the next part say 'Overruled' and Col H the Last part Say 'Unknown' Now if the next row down contains the same application Number but the subsequent rows contain the words 'Refused'-'Included' and then 'Unknown' I need to change the Unkinowns to a different result say 'Mickey Mouse' I can set up the code in VBA to check the 3 cells and change the last one depending on the criteria of the other 2 on the spreadsheet as a whole, I can write the formula on the spreadsheet to check col B for duplicates and return a word or ref, However what I cant do is marry the 2 together so that the only cells changed are those appertaining to the duplicate entries. In essence what I need is a bit of help getting the code to check Col B for duplicates then do the offset stuff on only those duplicate entries Cheers JR |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Cell Text/Data
"DaveO"
Not sure if my last reply has posted properly, I'm as you have probably guessed new to this stuff, Yes, The IF statements need to be true and I did forget to cut/paste the Loop bit, it is in the syntax on the sheet, I take it that the intcounter bit is 'Interval Counter' ? John R "DaveO" wrote: So the If statements all have to be true for anything to happen, is that correct? Also, I'm assuming the Loop at the end was omitted when you copied it over? "John R" wrote: "DaveO" Used your suggestion but unfortunately it still isn't playing, this is what I have input, Dim intCounter As Integer intCounter = 2 Do While Len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).Text = Range("B" & intCounter - 1).Text Then 'Do the cell alterations you want here If ActiveCell.Offset(0, 4).Value = "Refused" Then If ActiveCell.Offset(0, 5).Value = "Overruled" Then If ActiveCell.Offset(0, 6).Value = "REFNOAA" Then If ActiveCell.Offset(1, 4).Value = "Refused" Then If ActiveCell.Offset(1, 5).Value = "Included" Then If ActiveCell.Offset(1, 6).Value = "REFWITHAA" Then ActiveCell.Offset(0, 6).Value = "Partial" ActiveCell.Offset(1, 6).Value = "Partial" End If End If End If End If End If End If End If intCounter = intCounter + 1 can you (or anyone else) see where I'm going wrong Cheers John R "DaveO" wrote: It's possible. I'd suggest something like this... ----------------- dim intCounter as integer intCounter = 2 Do while len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).text = Range("B" & intCounter - 1).Text then {Do the cell alterations you want here} End If intCounter = intCounter + 1 Loop --------------------- All you need to do is use intCounter as your range reference and you should be OK. HTH. "John R" wrote: Hi, What I am doing at the mo is this:- I have a spread sheet which is used each month for reporting applications, the data is extracted from a database and contains some duplicates. I need to check theses duplicates against each other and ensure their result fields meet certain criteria. If they Do/Dont then I need to change/Not Change the data in the cells. For Instance, Col B contains the application numbers, col F Contains the first part of the test, say it contains the word 'Refused' Col G the next part say 'Overruled' and Col H the Last part Say 'Unknown' Now if the next row down contains the same application Number but the subsequent rows contain the words 'Refused'-'Included' and then 'Unknown' I need to change the Unkinowns to a different result say 'Mickey Mouse' I can set up the code in VBA to check the 3 cells and change the last one depending on the criteria of the other 2 on the spreadsheet as a whole, I can write the formula on the spreadsheet to check col B for duplicates and return a word or ref, However what I cant do is marry the 2 together so that the only cells changed are those appertaining to the duplicate entries. In essence what I need is a bit of help getting the code to check Col B for duplicates then do the offset stuff on only those duplicate entries Cheers JR |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Cell Text/Data
I have to be honest and say that I don't know why this isn't working. The
only thing I can think of, is that the data is not sorted on column 'B'. However, if you step through the code, after putting a break point in, if you hover the mouse over the statements in my IF do they appear to be the same? If so, why not step through one you would expect it to do the alterations on and see which part of the code is falling down. Other than this, I can't be of much more help. Perhaps just check that what you think should be happening actually matches the IFs you have entered. Let me know how it goes. HTH. "John R" wrote: "DaveO" Not sure if my last reply has posted properly, I'm as you have probably guessed new to this stuff, Yes, The IF statements need to be true and I did forget to cut/paste the Loop bit, it is in the syntax on the sheet, I take it that the intcounter bit is 'Interval Counter' ? John R "DaveO" wrote: So the If statements all have to be true for anything to happen, is that correct? Also, I'm assuming the Loop at the end was omitted when you copied it over? "John R" wrote: "DaveO" Used your suggestion but unfortunately it still isn't playing, this is what I have input, Dim intCounter As Integer intCounter = 2 Do While Len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).Text = Range("B" & intCounter - 1).Text Then 'Do the cell alterations you want here If ActiveCell.Offset(0, 4).Value = "Refused" Then If ActiveCell.Offset(0, 5).Value = "Overruled" Then If ActiveCell.Offset(0, 6).Value = "REFNOAA" Then If ActiveCell.Offset(1, 4).Value = "Refused" Then If ActiveCell.Offset(1, 5).Value = "Included" Then If ActiveCell.Offset(1, 6).Value = "REFWITHAA" Then ActiveCell.Offset(0, 6).Value = "Partial" ActiveCell.Offset(1, 6).Value = "Partial" End If End If End If End If End If End If End If intCounter = intCounter + 1 can you (or anyone else) see where I'm going wrong Cheers John R "DaveO" wrote: It's possible. I'd suggest something like this... ----------------- dim intCounter as integer intCounter = 2 Do while len(Range("B" & intCounter)) < 0 If Range("B" & intCounter).text = Range("B" & intCounter - 1).Text then {Do the cell alterations you want here} End If intCounter = intCounter + 1 Loop --------------------- All you need to do is use intCounter as your range reference and you should be OK. HTH. "John R" wrote: Hi, What I am doing at the mo is this:- I have a spread sheet which is used each month for reporting applications, the data is extracted from a database and contains some duplicates. I need to check theses duplicates against each other and ensure their result fields meet certain criteria. If they Do/Dont then I need to change/Not Change the data in the cells. For Instance, Col B contains the application numbers, col F Contains the first part of the test, say it contains the word 'Refused' Col G the next part say 'Overruled' and Col H the Last part Say 'Unknown' Now if the next row down contains the same application Number but the subsequent rows contain the words 'Refused'-'Included' and then 'Unknown' I need to change the Unkinowns to a different result say 'Mickey Mouse' I can set up the code in VBA to check the 3 cells and change the last one depending on the criteria of the other 2 on the spreadsheet as a whole, I can write the formula on the spreadsheet to check col B for duplicates and return a word or ref, However what I cant do is marry the 2 together so that the only cells changed are those appertaining to the duplicate entries. In essence what I need is a bit of help getting the code to check Col B for duplicates then do the offset stuff on only those duplicate entries Cheers JR |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing background colour when changing data in a cell | Excel Discussion (Misc queries) | |||
| Copy text from cell to cell with one cell changing text | Excel Worksheet Functions | |||
| Cell colors or text color changing when date in cell gets closer. | Excel Worksheet Functions | |||
| Changing the case of text data | Excel Worksheet Functions | |||
| Text Changing, based on data | Excel Discussion (Misc queries) | |||
Hybrid Mode
