Yes, this is much easier to understand.Did I miss where you mentioned your VBA
code before?I TOTALLY didn't understand that you were working with VBA.I
thought you were using Excel functions.
So, now that we're on the same page, let's take a look at your code:
When the macro first runs, colLast is set to 14 (column "N").
You then set the Rng variable to the Range of data in column "I".You then loop
through this Range (Rng) and process the values there.Now, the problem with:If
rCell.Value = "ELEC" Then rCell.Offset(0, colLast + 1).Value = "utility"
rCell.Offset means to move a specific distance from the cell rCell.
rCell is column "I"You used (0, colLast +1)that means move 0 rows (use current
row) but move 14+1 columns to the right from rCell!which puts it in column "AB"
instead of column "O".
You'd be better off using:Cells(rCell.Row, colLast + 1).Value = "utility"
Now, if you have more tests than these, I'd suggest going with an
If/ElseIf/Endif construct:
For Each rCell In Rng.Cells
If rCell.Value = "FUEL2" Then
Cells(rCell.Row, colLast + 1).Value = "Fuel"
ElseIf rCell.Value = "ELEC" Then
Cells(rCell.Row, colLast + 1).Value = "utility"
ElseIf rCell.Value = "GAS" Then
Cells(rCell.Row, colLast + 1).Value = "TEST"
End If Next rCell
Because, if rCell.Value is "FUEL2", then there's no reason to test if it is
"ELEC" or "GAS"!
Now, for your last question, you want to test:If Left(rCell.Value, 4) = "6183"
Then
What column are you looking in?Currently rCell is column "I", and this column
does not have numeric values.If you're looking in column "G", then I'd suggest
changing your macro to:------------------------- Dim colLast As Long
Dim Rng As Range, rCell As Range
'For first row. To change rows, alter the Cells number
colLast = Cells(1, Columns.Count).End(xlToLeft).Column
'Now for some data:
Cells(1, colLast + 1) = "TYPE"
Cells(1, colLast + 2) = "PMG"
Cells(2, colLast).Select
Set Rng = Range("I2:I" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each rCell In Rng.Cells
'--------------------------------------------------------
If rCell.Value = "FUEL2" Then
Cells(rCell.Row, colLast + 1).Value = "Fuel"
ElseIf rCell.Value = "ELEC" Then
Cells(rCell.Row, colLast + 1).Value = "utility"
ElseIf rCell.Value = "GAS" Then
Cells(rCell.Row, colLast + 1).Value = "TEST"
End If
'--------------------------------------------------------
If Left(Cells(rCell.Row, "G").Value, 4) = "6183" Then
Cells(rCell.Row, colLast + 2).Value = "AEP"
Else
Cells(rCell.Row, colLast + 2).Value = "ERP"
End If
'--------------------------------------------------------
Next rCell
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
On Wednesday, February 24, 2016 3:50 PM, Maju <betma...@gmail.com> wrote:
I am a bit confused with your question.
This is what I am able to come up with so far.
maybe you might be able to understand what I am trying to do from the code. So
I was able to come up with this but it is not placing the value in same come as
the word type Dim colLast As Long
Dim Rng As Range, rCell As Range 'For first row. To change rows, alter the
Cells number
colLast = Cells(1, Columns.Count).End(xlToLeft).Column
'Now for some data:
Cells(1, colLast + 1) = "TYPE"
Cells(2, colLast).Select
Set Rng = Range("I2:I" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each rCell In Rng.Cells
If rCell.Value = "FUEL2" Then rCell.Offset(0, colLast + 1).Value = "Fuel"
If rCell.Value = "ELEC" Then rCell.Offset(0, colLast + 1).Value =
"utility"
If rCell.Value = "GAS" Then rCell.Offset(0, colLast + 1).Value = "TEST"
Next rCellHow do I also incorporate this to include it in next column
after TYPE. Cells(1, colLast + 1) = "PMG" If Left(rCell.Value, 4) = "6183"
Then
rCell.Offset(0, 1).Value = "AEP"
Else
rCell.Offset(0, 1).Value = "ERP"
On Wednesday, February 24, 2016 at 1:35:02 PM UTC-5, Paul Schreiner wrote:
I'm sorry, I STILL do not understand.
How does the value in column "J" determine the value to display in column "O"?
as for "empty" rows.How can a row be empty if it has a value or something in
it?If it has something in it, it is not empty.If it is empty, it doesn't have
anything in it!?
I suspect I'm missing something in your explanation.
Paul------------------------------ -----------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
------------------------------ -----------
On Wednesday, February 24, 2016 11:19 AM, Maju <betm...@gmail.com> wrote:
I am using 2010 excel.
I have attached a file. The value in column J will determine what will go into
o. So I don't have the value for column o stored any where.
what I mean by delete row. Is to clean up the rows after the last row with
data. Sometimes some rows might be empty but have in value or something that
will make it thing that there is value.
thanks!
It would be helpful to provide a sample file.But:What version of Excel are you
using?
Where do you get the values for column "O"? Your example has entering "utility"
when something other than "fuel" is in column J.Are there other values?
And, what so you mean by "Delete any extra line after the last row with
data."?Excel 2010 allows 1,048,576 rows.You cannot "delete" any of these
rows.You can delete the CONTENTS, but the rows are still there.However, you can
HIDE any or all of these rows.
Can you please elaborate?
Paul------------------------------ -----------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
------------------------------ -----------
On Tuesday, February 23, 2016 8:48 AM, Maju <betm...@gmail.com> wrote:
Hello All,
Please I need help with the below macro.I have a worksheet with lots of data.
- I want to go through each cell in column J. To find data and put the
answer in columnO.
- Then left and Right trim spaces
- Delete any extra line after the last row with data.
- Save file as expense with a date attached to it.
The below should be the final result. Column J
ColumnO Fuel
Fuel Gas
UtilityElectric
UtilityFuel
Fuel
Your help will be greatly appreciated
thanks!!--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros...@ googlegroups.com.
To post to this group, send email to excel-...@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros...@ googlegroups.com.
To post to this group, send email to excel-...@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
