Need excel expert

K

kashif.special2005

Active Member
Joined
Oct 26, 2009
Messages
443
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
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:p> </o:p>
I am using this code but it is not working
<o:p> </o:p>
For j = last_row To 2 Step -1<o:p></o:p>
<o:p> </o:p>
cell_value = Cells(last_row, 1).Value<o:p></o:p>
<o:p> </o:p>
If IsNumeric(cell_value) = True Then
Next j<o:p></o:p>
Else<o:p></o:p>
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:p></o:p>
End If<o:p></o:p>
Next j
<o:p> </o:p>
<o:p> </o:p>
2<SUP>nd</SUP> Question:-<o:p></o:p>
<o:p> </o:p>
'Range("H2:H" & last_row).Formula = "=Value(TRIM(IF(ISTEXT(A2),MID(A2,2,LEN(A2)),A2)))"
<o:p> </o:p>
When I am applying above formula it is working fine, but when I am applying below formula it is giving me an error.
<o:p> </o:p>
Please help<o:p></o:p>
<o:p> </o:p>
'Range("H2:H" & last_row).Value=application.WorksheetFunction.TRIM(IF(ISTEXT(A2),MID(A2,2,LEN(A2)),A2))
<o:p> </o:p>
<o:p> </o:p>
Thanks,
Kashif.<o:p></o:p>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
for part 1 use:

Code: 
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)
 
Last edited:
Upvote 0
Before anything else ... why, oh why, oh why, do you never use a meaningful thread title?
 
Upvote 0
Hi Weaver,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
It is not working, it’s gives me an error that “Wrong number of arguments or invalid property assignment”
<o:p> </o:p>
GlennUK :- I am sorry, from next time I will do it.

Thanks,
Kashif.<o:p></o:p>
 
Upvote 0
kashif.special2005 said:
Hi,

I am still waiting for answer.....

Thanks,
Kashif.
Click to expand...
A bit impatient, aren't we?

For your first question, just simply your macro to this:
Code: 
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:
Code: 
Range("B4").Value = Trim(IIf(Not IsNumeric([b4]), Mid([b4], 2, Len([b4])), [b4]))
 
Last edited:
Upvote 0
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
It not working properly this loop converts all alphanumeric value into 0 keep numeric value as it is.
<o:p> </o:p>
Like
<o:p> </o:p>
<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:p> </o:p>
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:p> </o:p>
Value(TRIM(IF(ISTEXT(A2),MID(A2,2,LEN(A2)),A2)))
<o:p> </o:p>
Thanks,
Kashif.<o:p></o:p>
 
Upvote 0
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.
 
Upvote 0
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Run-Time Error 381
2
Replies
12
Views
198
Micron
M
P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
612
pure vito
P
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
338
tixarah
T
R
  • Question Question
The VBA that I am using to password protect an excel file asks to save a copy of the file while running, when I want it to pass protect the same file
Replies
1
Views
742
Jeffrey Mahoney
Jeffrey Mahoney
N
  • Solved
how to automatically clear text if text in another cell is removed
Replies
1
Views
141
Joe4
Joe4

Forum statistics

Threads
1,224,598
Messages
6,179,814
Members
452,945
Latest member
Bib195

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top