20. november 2006 - 11:14
Der er
1 løsning
Vb opdel adresse
Denne kode skal moddes lidt
1. man skal ikke mærke hvilken celler den skal opdele adresser fra.
Det skal den gøre i "L11:L40" Hvis der er værdi
2. Hvis der ikke er nr på adressen laver den fejl
der skal den bare skrive adressen som den skal og adde et 1 tal selv
Sub Deladd()
Range("L11:l40").Select
Range(Selection, Selection.End(xlDown)).Select
Dim s As String
Dim x As Long
Dim gade As String
Dim y As Variant
Dim HusnrOgBogstav As String
Dim Husnr
Dim Bogstav
Dim rest
Dim rngCell As Range
For Each rngCell In Selection
s = rngCell.value
y = Split(s, ",")
x = INSTRNUM(y(0))
gade = Mid(s, 1, x - 1)
HusnrOgBogstav = Mid(y(0), x, 99)
Husnr = Val(HusnrOgBogstav)
Bogstav = Trim(Replace(HusnrOgBogstav, Husnr, ""))
rest = IIf(UBound(y) = 0, "", y(UBound(y)))
rngCell.Offset(0, 1) = gade
rngCell.Offset(0, 2) = Husnr
rngCell.Offset(0, 3) = Bogstav
rngCell.Offset(0, 4) = rest
Next
End Sub
21. november 2006 - 09:52
#1
Sådan her gør jeg det bare
Sub stest()
Dim s As String
Dim x, o As Long
Dim gade As String
Dim y As Variant
Dim HusnrOgBogstav As String
Dim Husnr
Dim Bogstav
Dim rest
Dim rngCell As Range
For o = 11 To 40
If Cells(o, 12) > "" Then
Cells(o, 12).Select
End If
On Error Resume Next
For Each rngCell In Selection
s = rngCell.value
y = Split(s, ",")
x = INSTRNUM(y(0))
gade = Mid(s, 1, x - 1)
HusnrOgBogstav = Mid(y(0), x, 99)
Husnr = Val(HusnrOgBogstav)
Bogstav = Trim(Replace(HusnrOgBogstav, Husnr, ""))
rest = IIf(UBound(y) = 0, "", y(UBound(y)))
rngCell.Offset(0, 1) = gade
rngCell.Offset(0, 2) = Husnr
rngCell.Offset(0, 3) = Bogstav
rngCell.Offset(0, 4) = rest
Next
Next
End Sub
Function INSTRNUM(st As Variant) As Long
Dim I As Long, testchar As String
Do
I = I + 1
testchar = Mid(st, I, 1)
If testchar Like "[1-9]" Then
INSTRNUM = I
Exit Do
End If
Loop While I <= Len(st)
End Function
