Reading a cell value in Excel vba and write in another Cell

Question

I have an Excel file and I want to read a value of a cell i e a cell contains  S 1 P 0 K 1 Q 1  I want to read each value and save each value to another column  For example if S 1  then should be another cell 1  how can I read the data from  a cell and write in another cell with macro and vba   Thank you for your helps  UPDATE   Sub MacroF1   usedRowCount   Worksheets    bersicht 2013   UsedRange Rows Count For i   1 To usedRowCount cellAYvalue   Worksheets    bersicht 2013   Cells i   AY   Value If InStr cellvalue   S  1    lt  gt  0 Then Worksheets    bersicht 2013   Cells i   BC     1 Else Worksheets    bersicht 2013   Cells i   BC     0 End If  If InStr cellvalue   P  1    lt  gt  0 Then Worksheets    bersicht 2013   Cells i   BD     1 Else Worksheets    bersicht 2013   Cells i   BD     0 End If  If InStr cellvalue   M  1    lt  gt  0 Then Worksheets    bersicht 2013   Cells i   BE     1 Else Worksheets    bersicht 2013   Cells i   BE     0 End If  If InStr cellvalue   L  1    lt  gt  0 Then Worksheets    bersicht 2013   Cells i   BF     1 Else Worksheets    bersicht 2013   Cells i   BF     0 End If  If InStr cellvalue   K  1    lt  gt  0 Then Worksheets    bersicht 2013   Cells i   BG     1 Else Worksheets    bersicht 2013   Cells i   BG     0 End If  If InStr cellvalue   Q  1    lt  gt  0 Then Worksheets    bersicht 2013   Cells i   BH     1 Else Worksheets    bersicht 2013   Cells i   BH     0 End If  Worksheets    bersicht 2013   Cells i   BC     dd  Worksheets    bersicht 2013   Cells i   AY   Value  Worksheets    bersicht 2013   Range  BD44      Babak  Next i  End Sub

User · Accepted Answer

surely you can do this with worksheet formulas, avoiding VBA entirely:

so for this value in say, column AV S:1 P:0 K:1 Q:1

you put this formula in column BC:

=MID(AV:AV,FIND("S",AV:AV)+2,1)

then these formulas in columns BD, BE...

=MID(AV:AV,FIND("P",AV:AV)+2,1)
=MID(AV:AV,FIND("K",AV:AV)+2,1)
=MID(AV:AV,FIND("Q",AV:AV)+2,1)

so these formulas look for the values S:1, P:1 etc in column AV. If the FIND function returns an error, then 0 is returned by the formula, else 1 (like an IF, THEN, ELSE

Then you would just copy down the formulas for all the rows in column AV.

HTH Philip

User · Answer

I have this function for this case     Function GetValue r As Range  Tag As String  As Integer Dim c  nRet As String Dim n  x As Integer Dim bNum As Boolean  c   r Value n   InStr c  Tag  For x   n   1 To Len c    Select Case Mid c  x  1      Case         bNum   True     Case      Exit For     Case Else  If bNum Then nRet   nRet  amp  Mid c  x  1    End Select Next GetValue   val nRet  End Function   To fill cell BC      assumed that you check cell A1   Worksheets    bersicht 2013   Cells i   BC     GetValue range  A1    S

User · Answer

The individual alphabets or symbols residing in a single cell can be inserted into different cells in different columns by the following code   For i   1 To Len Cells 1  1   Cells 2  i    Mid Cells 1  1   i  1  Next   If you do not want the symbols like colon to be inserted put an if condition in the loop

[excel] Reading a cell value in Excel vba and write in another Cell

Examples related to excel

Examples related to vba