Combine Calculate in a cells with several calculate in that cell and after that another calculate based on that cell

U

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
496
Office Version
  1. 2019
Platform
  1. Windows
Hi guys
i have main problem and if you solve this, solve my main problem and helped me to calculate any of numbers that i do this in many hours
this is my problem
i have this worksheet that you see before, in first i said that belong to text, but that have information for main calculate, better say
in one cell i write information with this format : Give (+ Value) or Receive (-Value) ,G Or M, Text, Number, Percent or Thousands or Hundred (if Give and G and Percent: Percent that multiply to Number that written before this (if i not write anything in this part, multiply with 1), if Give and G and Thousands: Thousand multiply with number, if Receive and G and Percent: Percent multiply with number, if Receive and G and Hundred: Hundred multiply with number and then divide 750, for Give M that all this value sum and fill in specific columns that i say in below but for Receive M, if write MM after that calculate: Number multiply MM and divide 4.3318 if not write like Give M Calculate)
This fill in Column A
Columns B:C fill with Text without calculate
but in Column D
This fills with SUM of Gives and Back (+ Value) Calculates from G
in Column E
fills with SUM of Receive (- Value) Calculates from G
in Column F
fills with SUM of Gives (+ Value) Calculates from M
in Column G
fills with SUM of Receive (-Value) Calculates from M
and a point, i can't split information that belong one cell, because all of the info about a day and should write all info in one cell
 

Attachments

  • image_2021-07-16_183906.png
    image_2021-07-16_183906.png
    20.9 KB · Views: 24
Sort by date Sort by votes
maabadi said:
Questions:

1. Are you always have at Cell A6 Equal sign (=) at each line?
2. At Cell A6
Rich (BB code): 
calcuate eace item
item 1 = +1 * 23 + (23 * 10%) = A1
item 2 = +1 * 12 =B1 * 23000 = B2
item 3 = -1 * 56 + (56 * 8%) = C1
item 4 = -1 * 43 = (D1) * 11000 = D2
item 5 = +1 * 1200000 = E1
item 6 = +1 * 2300000 / 3300000 * 4.3318 = F1
item 7 = -1 * 1200000 = G1
item 8 = -1 * 2300000 / 3300000 * 4.3318 = H1

A. What is B1? I see text at Cell B1 & I don't Know How Calculate B1.
B. Same For D1?
C. At Item 6 & 8 : please define exact value you want with Parentheses?
For example is Item 6 is ;
Rich (BB code): 
 item 6 = (+1 * 2300000) / (3300000 * 4.3318) = F1
OR
Rich (BB code): 
 item 6 = (+1 * 2300000 / 3300000) * 4.3318 = F1
Click to expand...
1. no this is an example cell and i define items just for a sample
2.
A.B1=+1*12=12 B2=B1*23000=276000
B.D1=-1*43=-43 D2=D1*11000=-473000
C.item 6 = (+1 * 2300000 / 3300000) * 4.3318 = F1 this is the correct order of calculate you write, for 8 like this order
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
I write this primary code. Please Test it. But I have this Problems. Please Answer these questions:
1. You have one Worksheet Change Event at your file. Do You Need it?
2. Do you want this code as Normal Code or Worksheet Change Events?
3. I Differentiate Case 1 & 2 with % Symbol ( Same For Case 3 & 4). But I have problem to make differentiate between Case 5 & 6 (Also 7 & 8).
Then I use number of characters for each line, but if number of digits for numbers changes for different Cases, we should add one symbol for them.
4. if you want to add results to Cells at row 6, I add it at next code.
This is Normal Code:
VBA Code: 
Sub FindValues()
Dim A As Double, B As Double, C As Double, D As Double, E As Double, F As Double, G As Double, H As Double
Dim I As String, J As Double, K As String, L As Double, M As Long, N As Long, O As Long
Dim T As String, P As Long, R As Long, S1 As Double, S2 As Double, W As Long, X As Long, Y As Long

T = Range("A6").Value
X = Len(T) - Len(Replace(T, "=", ""))
For M = 1 To X
W = Application.WorksheetFunction.Find(Chr(1), Application.WorksheetFunction.Substitute(T, "=", Chr(1), M))
  If M < X Then
   Y = Application.WorksheetFunction.Find(Chr(1), Application.WorksheetFunction.Substitute(T, "=", Chr(1), M + 1)) - 1
  Else
   Y = Len(T) + 6
  End If
I = Mid(T, W + 2, 1)
K = Mid(T, W + 4, 1)
O = W + 4
For N = O To Y
If Mid(T, N, 1) = " " And IsNumeric(Mid(T, N + 1, 1)) Then P = N + 1
If IsNumeric(Mid(T, N, 1)) And Not IsNumeric(Mid(T, N + 1, 1)) Then R = N
 If I = "!" Then
    If K = "#" Then
        If R >= P And P > 0 Then
            If S1 = 0 Then
            S1 = Mid(T, P, R - P + 1)
            N = N + 2
            Else
            S2 = Mid(T, P, R - P + 1)
            End If
        End If
        If S2 <> S1 And S2 > 0 Then
            If Mid(T, R + 2, 1) = "%" Then
            A = S1 * (1 + S2 / 100)
            Else
            B = S1 * S2
            J = S1
            End If
            
        S1 = 0
        S2 = 0
        P = 0
        R = 0
        GoTo Resum1
        End If
    ElseIf K = "$" Then
         If Y - W < 34 Then
            If R >= P And P > 0 Then
              E = Mid(T, P, R - P + 1) * 1
              P = 0
              R = 0
              GoTo Resum1
             End If
         Else
            If R >= P And P > 0 Then
                If S1 = 0 Then
                S1 = Mid(T, P, R - P + 1)
                N = N + 2
                Else
                S2 = Mid(T, P, R - P + 1)
                End If
            End If
            If S2 <> S1 And S2 > 0 Then
             F = (S1 / S2) * 4.3318
             S1 = 0
             S2 = 0
             P = 0
             R = 0
             GoTo Resum1
            End If
         End If
    End If
 ElseIf I = "@" Then
    If K = "#" Then
        If R >= P And P > 0 Then
            If S1 = 0 Then
            S1 = Mid(T, P, R - P + 1)
            N = N + 2
            Else
            S2 = Mid(T, P, R - P + 1)
            End If
        End If
        If S2 <> S1 And S2 > 0 Then
            If Mid(T, R + 2, 1) = "%" Then
            C = S1 * (1 + S2 / 100) * -1
            Else
            D = S1 * S2 * -1
            L = S1 * -1
            End If
         S1 = 0
         S2 = 0
         P = 0
         R = 0
         GoTo Resum1
        End If
    ElseIf K = "$" Then
        If Y - W < 34 Then
            If R >= P And P > 0 Then
              G = Mid(T, P, R - P + 1) * -1
              P = 0
              R = 0
              GoTo Resum1
             End If
        Else
            If R >= P And P > 0 Then
                If S1 = 0 Then
                S1 = Mid(T, P, R - P + 1)
                N = N + 2
                Else
                S2 = Mid(T, P, R - P + 1)
                End If
            End If
            If S2 <> S1 And S2 > 0 Then
             H = (S1 / S2) * -4.3318
             S1 = 0
             S2 = 0
             P = 0
             R = 0
             GoTo Resum1
            End If
        End If
    End If
 End If
Next N
Resum1:
Next M
MsgBox "A1 = " & A & vbCr & "B1 = " & J & " // " & "B2 = " & B & vbCr & "C1 = " & C & vbCr & "D1 = " & L & " // " & "D2 = " & D _
& vbCr & "E1 = " & E & vbCr & "F1 = " & F & vbCr & "G1 = " & G & vbCr & "H1 = " & H
End Sub
 
Upvote 0
1. Are you always have at Cell A6 Equal sign (=) at each line?
Click to expand...
1. no this is an example cell and i define items just for a sample
Click to expand...
The Above Code Write based Equal sign at each line. I need the real structure for Example to Know how change macro based on.
 
Upvote 0
maabadi said:
I write this primary code. Please Test it. But I have this Problems. Please Answer these questions:
1. You have one Worksheet Change Event at your file. Do You Need it?
2. Do you want this code as Normal Code or Worksheet Change Events?
3. I Differentiate Case 1 & 2 with % Symbol ( Same For Case 3 & 4). But I have problem to make differentiate between Case 5 & 6 (Also 7 & 8).
Then I use number of characters for each line, but if number of digits for numbers changes for different Cases, we should add one symbol for them.
4. if you want to add results to Cells at row 6, I add it at next code.
This is Normal Code:
VBA Code: 
Sub FindValues()
Dim A As Double, B As Double, C As Double, D As Double, E As Double, F As Double, G As Double, H As Double
Dim I As String, J As Double, K As String, L As Double, M As Long, N As Long, O As Long
Dim T As String, P As Long, R As Long, S1 As Double, S2 As Double, W As Long, X As Long, Y As Long

T = Range("A6").Value
X = Len(T) - Len(Replace(T, "=", ""))
For M = 1 To X
W = Application.WorksheetFunction.Find(Chr(1), Application.WorksheetFunction.Substitute(T, "=", Chr(1), M))
  If M < X Then
   Y = Application.WorksheetFunction.Find(Chr(1), Application.WorksheetFunction.Substitute(T, "=", Chr(1), M + 1)) - 1
  Else
   Y = Len(T) + 6
  End If
I = Mid(T, W + 2, 1)
K = Mid(T, W + 4, 1)
O = W + 4
For N = O To Y
If Mid(T, N, 1) = " " And IsNumeric(Mid(T, N + 1, 1)) Then P = N + 1
If IsNumeric(Mid(T, N, 1)) And Not IsNumeric(Mid(T, N + 1, 1)) Then R = N
 If I = "!" Then
    If K = "#" Then
        If R >= P And P > 0 Then
            If S1 = 0 Then
            S1 = Mid(T, P, R - P + 1)
            N = N + 2
            Else
            S2 = Mid(T, P, R - P + 1)
            End If
        End If
        If S2 <> S1 And S2 > 0 Then
            If Mid(T, R + 2, 1) = "%" Then
            A = S1 * (1 + S2 / 100)
            Else
            B = S1 * S2
            J = S1
            End If
          
        S1 = 0
        S2 = 0
        P = 0
        R = 0
        GoTo Resum1
        End If
    ElseIf K = "$" Then
         If Y - W < 34 Then
            If R >= P And P > 0 Then
              E = Mid(T, P, R - P + 1) * 1
              P = 0
              R = 0
              GoTo Resum1
             End If
         Else
            If R >= P And P > 0 Then
                If S1 = 0 Then
                S1 = Mid(T, P, R - P + 1)
                N = N + 2
                Else
                S2 = Mid(T, P, R - P + 1)
                End If
            End If
            If S2 <> S1 And S2 > 0 Then
             F = (S1 / S2) * 4.3318
             S1 = 0
             S2 = 0
             P = 0
             R = 0
             GoTo Resum1
            End If
         End If
    End If
 ElseIf I = "@" Then
    If K = "#" Then
        If R >= P And P > 0 Then
            If S1 = 0 Then
            S1 = Mid(T, P, R - P + 1)
            N = N + 2
            Else
            S2 = Mid(T, P, R - P + 1)
            End If
        End If
        If S2 <> S1 And S2 > 0 Then
            If Mid(T, R + 2, 1) = "%" Then
            C = S1 * (1 + S2 / 100) * -1
            Else
            D = S1 * S2 * -1
            L = S1 * -1
            End If
         S1 = 0
         S2 = 0
         P = 0
         R = 0
         GoTo Resum1
        End If
    ElseIf K = "$" Then
        If Y - W < 34 Then
            If R >= P And P > 0 Then
              G = Mid(T, P, R - P + 1) * -1
              P = 0
              R = 0
              GoTo Resum1
             End If
        Else
            If R >= P And P > 0 Then
                If S1 = 0 Then
                S1 = Mid(T, P, R - P + 1)
                N = N + 2
                Else
                S2 = Mid(T, P, R - P + 1)
                End If
            End If
            If S2 <> S1 And S2 > 0 Then
             H = (S1 / S2) * -4.3318
             S1 = 0
             S2 = 0
             P = 0
             R = 0
             GoTo Resum1
            End If
        End If
    End If
 End If
Next N
Resum1:
Next M
MsgBox "A1 = " & A & vbCr & "B1 = " & J & " // " & "B2 = " & B & vbCr & "C1 = " & C & vbCr & "D1 = " & L & " // " & "D2 = " & D _
& vbCr & "E1 = " & E & vbCr & "F1 = " & F & vbCr & "G1 = " & G & vbCr & "H1 = " & H
End Sub
Click to expand...
things about:
1.this is not just for A6, this about Column A and this find out from A Cells
2.equal sign not used in all of this A Cells
3.two worksheet change used in this sheet that one of them about create customer that this is should keep, but another about *-1 value belong E and G Columns, this is not used with this new code you define (i send you another file that you can see this codes i say about)
4.for question number 3, i test it in my worksheet but i not find out how this code is work and i should use what letters to this correctly work
5.can i use text after this symbol and can change align to right? (like photo) and should separate between symbols? if should this is my formats

! ، # a few words ، Number (that liked ###.### or ##.### or #.###) ، number %
! ، # a few words ، Number (that liked ###.### or ##.### or #.###) ، thousands number
@ ، # a few words ، Number (that liked ###.### or ##.### or #.###) ، number %
@ ، # a few words ، Number (that liked ###.### or ##.### or #.###) ، thousands number
! ، $ a few words ، milions number
! ، $ a few words ، milions number ، a few words then milions number
! ، @ a few words ، milions number
! ، @ a few words ، milions number ، a few words then milions number

*thousands number and milions number like all separate like this ###,###,###


drive.google.com

Book1 (1).xlsm

drive.google.com drive.google.com
 

Attachments

  • image_2021-08-10_112524.png
    image_2021-08-10_112524.png
    20.6 KB · Views: 5
Upvote 0
Unexpc said:
! ، # a few words ، Number (that liked ###.### or ##.### or #.###) ، number %
! ، # a few words ، Number (that liked ###.### or ##.### or #.###) ، thousands number
@ ، # a few words ، Number (that liked ###.### or ##.### or #.###) ، number %
@ ، # a few words ، Number (that liked ###.### or ##.### or #.###) ، thousands number
! ، $ a few words ، milions number
! ، $ a few words ، milions number ، a few words then milions number
! ، @ a few words ، milions number
! ، @ a few words ، milions number ، a few words then milions number
Click to expand...
sorry last two formats wrong, this is correct
@ ، $ a few words ، milions number
@ ، $ a few words ، milions number ، a few words then milions number
 
Upvote 0
1. Please give me new examples without item names
For make different between case 5 & 6 , I try to find one way.
 
Upvote 0
maabadi said:
1. Please give me new examples without item names
For make different between case 5 & 6 , I try to find one way.
Click to expand...
please check it, i add a few words and i write ا just for align to right, this is not write in main work sheet
docs.google.com

Book1 (1).xlsx

Sheet1
docs.google.com docs.google.com
 
Last edited:
Upvote 0
and i not separate thousand and ###.### but in main i use it but that is not effect in result? i mean i should write ###.### or ##.### to do correct and just for thousand should automatic separate...
 
Upvote 0
Unexpc said:
and i not separate thousand and ###.### but in main i use it but that is not effect in result? i mean i should write ###.### or ##.### to do correct and just for thousand should automatic separate...
Click to expand...
1. at this situation you use Persian comma or English comma? VBA don't recognize Persian Comma.
2. Please Add one Special Character to we use it at first Character for each case & Separate it from Next or Previous Case e.g. &
3. For Decimal Character You Use Dot or Slash? ( . or / )
 
Upvote 0
maabadi said:
1. at this situation you use Persian comma or English comma? VBA don't recognize Persian Comma.
2. Please Add one Special Character to we use it at first Character for each case & Separate it from Next or Previous Case e.g. &
3. For Decimal Character You Use Dot or Slash? ( . or / )
Click to expand...
1.i use persian comma, for thousands can't use english comma? i don't think have problem with english comma that is bad show?
2.at first i use الف for aligned to right, should use specific character at first?(i want when i write ! or @ at first, this aligned to right not left) and you say use & for separate? there is no problem with it and good idea instead of comma
3. i use Dot
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Analytics Project - Sumifs
Replies
1
Views
261
bobaol
B
S
  • Solved
Calculating Multiple Columns with SUMIFS
Replies
3
Views
243
Joe4
Joe4
B
  • Question
Excel 2021 - Sum cells that contain text for total and total by month
Replies
1
Views
92
bebo021999
B
K
  • Solved
Question Only? why one vba code works and another wont, even though should have same results.
Replies
3
Views
93
MARK858
MARK858
R
  • Solved
Connect periods with spilled result, where the result is same as the calculated result in previous row
2
Replies
15
Views
213
roelandwatteeuw
R

Forum statistics

Threads
1,215,415
Messages
6,124,768
Members
449,187
Latest member
hermansoa

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