Read Data 2

[komark]

Hi,
I need to write a vba macro that would read the data from a certain column and store it in a variable and add 4 to it.

For Example: if I have 11 in that column than it should add 4 and show 15.
Likewise if I have something like 20FF+20d. the macro should start reading that and when it hits the word (F) right after the integer (20) it should stop. Store that value 20 and add 4 to it. Then is should represent 24FF+20d.

another example: 9SF+1d should change to 13SF+1d.

any ideas..anyone?

 

[GlennUK]

Use a For-Next loop to go through the cells of the column, and an inner loop to test the contents of each cell, using the ASC function to see if the character being tested is outside of the range 48 to 57 ... if so it's not a number.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[vbap]

Here's an approach. You can modify to suit your needs. I assumed that you wanted it to stop at any character after the prefix value you're seeking.

Sub tst()
For Each c In Selection
Var = Trim(c.Value)
s = Val(Var) + 4
Mid(Var, 1) = s
c.Value = Var
Next
End Sub

 

[PHV]

A starting point:
c = Replace(c, Val(c), 4+Val(c), 1, 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vbap]

I like it PHV.

 

[komark]

Vbap
I get an invalid procedure call on
Mid(Var, 1) = s

 

[komark]

This is what I have so far

Sub test()



For x = 10 To 1 Step -1
Cells(x, "B").Select
For Each c In Selection
Var = Trim(c.Value)
s = Val(Var) + 4
Mid(Var, 1) = s
c.Value = Var
Next

'c = Replace(c, Val(c), 4 + Val(c), 1, 1)
Next

End Sub

 

[vbap]

What range of cells to you wish it to run through? The range can be any column or retangular range of cells, etc.

Sub tst()
Dim rg As Range
Dim ws As Worksheet

col = 2
Set ws = ActiveSheet
Set rg = ws.Range(ws.Cells(1, col), ws.Cells(10, col))
For Each c In rg
Var = Trim(c.Value)
s = Val(Var) + 4
Mid(Var, 1) = s
c.Value = Var
Next
End Sub

or use PHV's replacement:

Sub tst()
Dim rg As Range
Dim ws As Worksheet

col = 2
Set ws = ActiveSheet
Set rg = ws.Range(ws.Cells(1, col), ws.Cells(10, col))
For Each c In rg
c.Value = Replace(c, Val(c), 4 + Val(c), 1, 1)
Next
End Sub

 

[vbap]

If you encounter a blank cell you'll get the invalid procedural call. You'll have to add a conditional statement...If Then...End IF. I assumed there were no blank cells or cells with literal spaces in the range. Also you might want to change Mid(Var,1) = Trim(String(s)), but it doesn't seem to hurt to leave as is.


Sub test()



For x = 10 To 1 Step -1
Cells(x, "B").Select
For Each c In Selection
Var = Trim(c.Value)
IF Var<>"" then
s = Val(Var) + 4
Mid(Var, 1) = s
c.Value = Var
End IF
Next

'c = Replace(c, Val(c), 4 + Val(c), 1, 1)
Next

End Sub

 

[komark]

Vbap,
Thank you so much for you help. Although I am having another problem trying to solve this issue of mine.
Some of the data now i am getting are comma seperated. Like:
20FF+20d, 10

I need to change that to : 24FF+20d, 14.

 

[vbap]

When there is a comma...is there always one comma? You can use something like this if there is one comma or no comma.

For Each c In rg
s = Trim(c.Value)
If s <> "" Then
s1 = ParseString(s, ",")
s1 = Add4toPrefixValue(s1)
If s <> "" Then
s2 = Add4toPrefixValue(s)
c.Value = s1 & ", " & s2
Else
c.Value = s1
End If
End If
Next


Function Add4toPrefixValue(ByVal s) As Variant
Add4toPrefixValue = Replace(s, Val(s), 4 + Val(s), 1, 1)
End Function

'A handy function to keep around. This funtion parses
'a string using a character delimiter or string delimiter.
Function ParseString(ByRef s, ByVal delimiter) As Variant
If Len(s) <> 0 Then
n = InStr(s, delimiter)
L = Len(delimiter)
If n <> 0 Then
ParseString = Trim(Left(s, n - 1))
s = Trim(Mid(s, n + L))
Else
ParseString = Trim(s)
s = ""
End If
Else
ParseString = ""
End If
End Function

 

[komark]

Hey Vbap,
There might be more than one commas. How can I change your program to do that.

 

[PHV]

For Each c In rg
a = Split(c, ",")
For i = 0 To UBound(a)
a(i) = Replace(a(i), Val(a(i)), 4+Val(a(i)), 1, 1)
Next
c = Join(a, ",")
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[komark]

Hi PHV

This is what I have. I have added your part of the program but it is not doing anything.

Sub tst()


For x = 114 To 1 Step -1
Cells(x, "C").Select

For Each c In Selection
a = Split(c, ",")
For i = 0 To UBound(a)
a(i) = Replace(a(i), Val(a(i)), 4 + Val(a(i)), 1, 1)
Next
c = Join(a, ",")
Next

Next
End Sub


Function Add4toPrefixValue(ByVal s) As Variant
Add4toPrefixValue = Replace(s, Val(s), 4 + Val(s), 1, 1)
End Function

'A handy function to keep around. This funtion parses
'a string using a character delimiter or string delimiter.
Function ParseString(ByRef s, ByVal delimiter) As Variant
If Len(s) <> 0 Then
n = InStr(s, delimiter)
L = Len(delimiter)
If n <> 0 Then
ParseString = Trim(Left(s, n - 1))
s = Trim(Mid(s, n + L))
Else
ParseString = Trim(s)
s = ""
End If
Else
ParseString = ""
End If
End Function

 

[PHV]

Sub tst()
For x = 114 To 1 Step -1
a = Split(Cells(x, "C").Value, ",")
For i = 0 To UBound(a)
a(i) = Replace(a(i), Val(a(i)), 4 + Val(a(i)), 1, 1)
Next
Cells(x, "C").Value = Join(a, ",")
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[komark]

Thank you so much for you guy's help. It is working great.

-regards

 

[komark]

PHV,
Why does program only runs through the first 5 comma seperated numbers.

For something like this: 10,4,24+1d,10,5,6,7
I get: 14,8,28+1d,14,9,0,0

 

[PHV]

Works for me.
Here what I typed and got in the debug window:
s="10,4,24+1d,10,5,6,7"
a=Split(s, ",")
for i=0 to ubound(a): a(i) = Replace(a(i), Val(a(i)), 4+Val(a(i)), 1, 1): next
? Join(a, ",")
14,8,28+1d,14,9,10,11

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[komark]

PHV,
This is the code I have
Sub tst()

For x = 180 To 1 Step -1
a = Split(Cells(x, "F").Value, ",")
For i = 0 To UBound(a)
a(i) = Replace(a(i), Val(a(i)), 4 + Val(a(i)), 1, -1)
Next
Cells(x, "F").Value = Join(a, ",")
Next

End Sub

and this is what I am getting on one of the cells
121,122,123,124,125,000,000,000

 

[PHV]

Replace this:
a(i) = Replace(a(i), Val(a(i)), 4 + Val(a(i)), 1, -1)
with this (as I posted):
a(i) = Replace(a(i), Val(a(i)), 4 + Val(a(i)), 1, 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886