Need excel expert

[kashif.special2005]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
1<SUP>st</SUP> Question:- I am applying loop in cells that if cells value is alphanumeric than convert it into numeric and if cells value is numeric than next loop
<o> </o>
I am using this code but it is not working
<o> </o>
For j = last_row To 2 Step -1<o></o>
<o> </o>
cell_value = Cells(last_row, 1).Value<o></o>
<o> </o>
If IsNumeric(cell_value) = True Then
Next j<o></o>
Else<o></o>
Cells(last_row, 1).Value = Value(Trim(Mid(Cells(last_row, 1), Cells(last_row, 1), Len(Cells(last_row, 1))), Cells(last_row, 1)))<o></o>
End If<o></o>
Next j
<o> </o>
<o> </o>
2<SUP>nd</SUP> Question:-<o></o>
<o> </o>
'Range("H2:H" & last_row).Formula = "=Value(TRIM(IF(ISTEXT(A2),MID(A2,2,LEN(A2)),A2)))"
<o> </o>
When I am applying above formula it is working fine, but when I am applying below formula it is giving me an error.
<o> </o>
Please help<o></o>
<o> </o>
'Range("H2:H" & last_row).Value=application.WorksheetFunction.TRIM(IF(ISTEXT(A2),MID(A2,2,LEN(A2)),A2))
<o> </o>
<o> </o>
Thanks,
Kashif.<o></o>

 

[Weaver]

for part 1 use:

For j = last_row To 2 Step -1
cell_value = Cells(j, 1).Value
If IsNumeric(cell_value) = false Then
Cells(last_row, 1).Value = Value(Trim(Mid(Cells(last_row, 1), Cells(last_row, 1), Len(Cells(last_row, 1))), Cells(last_row, 1)))
End If
Next j

best not to have 2 nexts for 1 for.

Plus you were checking the last_row cell in your loop (static) rather than cell on row j which I assume was the intention of this particular loop variable.

I've not tested this, so your problem may lie elsewhere

Second question VB doesn't understand meaning of A2

Plus vb won't update multiple values in this way (I don't think)

 

[GlennUK]

Before anything else ... why, oh why, oh why, do you never use a meaningful thread title?

 

[Weaver]

Before anything else ... why, oh why, oh why, do you never use a meaningful thread title?

+1

I guess I've got so used to this that I don't notice any more!

 

[kashif.special2005]

Hi Weaver,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
It is not working, it’s gives me an error that “Wrong number of arguments or invalid property assignment”
<o> </o>
GlennUK :- I am sorry, from next time I will do it.

Thanks,
Kashif.<o></o>

 

[kashif.special2005]

Hi,

I am still waiting for answer.....

Thanks,
Kashif.

 

[GlennUK]

Hi,

I am still waiting for answer.....

Thanks,
Kashif.

A bit impatient, aren't we?

For your first question, just simply your macro to this:

last_row = Cells(Rows.Count, 1).End(xlUp).Row

For j = last_row To 2 Step -1

        Cells(j, 1).NumberFormat = "General"
        Cells(j, 1).Value = Val(Cells(j, 1).Value)
    
Next j

As for your second question, of course it won't work ... you've changed the Trim to be called by WorkSheetFunction, but not the IF function call, or the TEXT function call, and so on. By the way, there is a Trim VBA function, an Iif vba function, and Format vba function can be used instead of worksheet function TEXT, in case you want to do more experiments in that direction.

An example:

Range("B4").Value = Trim(IIf(Not IsNumeric([b4]), Mid([b4], 2, Len([b4])), [b4]))

 

[kashif.special2005]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
It not working properly this loop converts all alphanumeric value into 0 keep numeric value as it is.
<o> </o>
Like
<o> </o>
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 48pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=64 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>X1189
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>X1189
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>Y5102
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #d4d0c8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>Y5102
</TD></TR></TBODY></TABLE>
<o> </o>
This loop converts all these type of value into, but I want to remove only alpha characters from these type of values with mid functions like
<o> </o>
Value(TRIM(IF(ISTEXT(A2),MID(A2,2,LEN(A2)),A2)))
<o> </o>
Thanks,
Kashif.<o></o>

 

[kashif.special2005]

Hi,

I am using this loop, but this is also not working

last_row = Cells(Rows.Count, 1).End(xlUp).Row

For u = last_row To 2 Step -1
Cells(last_row, 1).Value = Trim(IIf(Not IsNumeric([Cells(last_row, 1)]), Mid([Cells(last_row, 1)], 2, Len([Cells(last_row, 1)])), [Cells(last_row, 1)]))
Next u

Thanks,
Kashif.

 

[GlennUK]

For j = last_row To 2 Step -1
    
        Cells(j, 1).Value = Trim(IIf(Not IsNumeric(Cells(j, 1).Value), Mid(Cells(j, 1).Value, 2, Len(Cells(j, 1).Value)), Cells(j, 1).Value))
    
Next j