VBA macros in Excel tables split the numbers in two cells conditionally

Source: Internet
Author: User

There's a friend. The A1 cells and B1 cells in the Excel worksheet have two digits, and the two numbers are the same, now you want to find the same number and write to cell C1, find the numbers in the A1 that are not in the B1 and write to cell D1. Find the numbers in the B1 that are not in the A1 and write to cell E1.

As in the following worksheet picture:

I don't know whether the numbers given are the same rule, that is, the number of the first raw data is the same as the previous number of the second original data. If this is the case, then the following code can be given to this specific example:

Sub Separatenumber ()

Dim Strfirst as String

Dim Strresult as String

Dim Startnum as Integer

Dim Endnum as String

Dim I As Integer, J As Integer

Strfirst = Left (Range ("B1″), 1)

Startnum = INSTR (1, Range ("A1″), Strfirst)

j = 1

For i = Startnum to Len (Range ("A1″)

Endnum = Mid (Range ("A1″), I, 1)

If Endnum = Left (Range ("B1″), J) Then

j = j + 1

End If

Next I

If J > 1 Then

strresult = Mid (Range ("A1″), Startnum, i-1)

End If

' The data in cell C1

Range ("C1″"). Value = Strresult

' The data in cell D1

Range ("D1″"). Value = Left (Range (A1″), StartNum-1)

' The data in cell E1

Range ("E1″"). Value = Right (range (B1″), Len (range ("B1″)"-j)

End Sub

The code is simple, using just a few VBA functions.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.