Question description:
This user has given permission to use the problem statement for this
blog.
i need help to understand this
If Left(rngArr(i, j), 1) <> "P"
Solved by S. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
22/11/2017 - 09:07
hi
Excelchat Expert
22/11/2017 - 09:07
Hello there, welcome to Got It Pro
User
22/11/2017 - 09:08
i just to understand this formula
User
22/11/2017 - 09:08
If Left(rngArr(i, j), 1) <> "P"
Excelchat Expert
22/11/2017 - 09:08
This is VBA?
User
22/11/2017 - 09:08
yes
Excelchat Expert
22/11/2017 - 09:09
rngArr is an array, with 2 dimensions
Excelchat Expert
22/11/2017 - 09:09
rngArr(i,j) will retrieve an element in the Array, which is a string
Excelchat Expert
22/11/2017 - 09:10
Left(rngArr(i,j),1) will then extract a single Character from the retrieve string
Excelchat Expert
22/11/2017 - 09:10
If Left(rngArr(i,j),1) <> "P"
User
22/11/2017 - 09:11
and check if it is not equal to P?
Excelchat Expert
22/11/2017 - 09:11
checks whether the extracted Character is not equal to "P"
Excelchat Expert
22/11/2017 - 09:11
Yes
User
22/11/2017 - 09:11
what if i want the same formula but instead of not equal to P, i want it to check if the string is not available in a group of cells
User
22/11/2017 - 09:12
string = Blue, Cells = Yellow & Red
User
22/11/2017 - 09:12
so it will not match
Excelchat Expert
22/11/2017 - 09:13
Then you need to loop through the cells and check if there is no match
User
22/11/2017 - 09:13
my cells are from A1 to A107
Excelchat Expert
22/11/2017 - 09:13
Lets assume the cells were in the range A1:A107
User
22/11/2017 - 09:13
how can i write the formula?
Excelchat Expert
22/11/2017 - 09:13
wait
Excelchat Expert
22/11/2017 - 09:13
dim rng as Range
Excelchat Expert
22/11/2017 - 09:14
set rng = ActiveSheet.Range("A1:A107")
Excelchat Expert
22/11/2017 - 09:14
dim cell as Range
Excelchat Expert
22/11/2017 - 09:14
for each cell in rng
User
22/11/2017 - 09:15
so i should write these 3 lines?
Excelchat Expert
22/11/2017 - 09:15
yes I am not finished
User
22/11/2017 - 09:15
oh ok
Excelchat Expert
22/11/2017 - 09:15
what do you want to do if the value is found?
Excelchat Expert
22/11/2017 - 09:15
if there is a match
User
22/11/2017 - 09:16
i have the other formula ready
User
22/11/2017 - 09:16
in vba
User
22/11/2017 - 09:16
i just want this match
Excelchat Expert
22/11/2017 - 09:16
what should the program do if there was a match?
User
22/11/2017 - 09:16
If cnt = nmbr Then
viceversa = rngArr(i, j)
Exit Function
Else
cnt = cnt + 1
End If
Else
temp = viceversa(rngArr(i, j), rng, nmbr, True)
If temp <> "" Then
viceversa = temp
Exit Function
End If
End If
Next j
End If
Next i
viceversa = ""
End Function
User
22/11/2017 - 09:17
this
User
22/11/2017 - 09:17
it is working i just wanted the match
User
22/11/2017 - 09:17
dim rng as Range
set rng = ActiveSheet.Range("A1:A107")
dim cell as Range
for each cell in rng
User
22/11/2017 - 09:18
these are them right?
Excelchat Expert
22/11/2017 - 09:18
yes
User
22/11/2017 - 09:18
okay thanks alot
Excelchat Expert
22/11/2017 - 09:19
You need to wrap this around your matching part
Excelchat Expert
22/11/2017 - 09:19
for each cell in rng
Excelchat Expert
22/11/2017 - 09:19
....
Excelchat Expert
22/11/2017 - 09:19
....
Excelchat Expert
22/11/2017 - 09:19
next cell
Excelchat Expert
22/11/2017 - 09:19
Then for the matching its
Excelchat Expert
22/11/2017 - 09:20
If Left(rngArr(i,j),1) <> cell.vale
Excelchat Expert
22/11/2017 - 09:20
If Left(rngArr(i,j),1) <> cell.value
User
22/11/2017 - 09:21
If Left(rng(i,j),1) <> cell.value
User
22/11/2017 - 09:21
not this?
Excelchat Expert
22/11/2017 - 09:21
OK, just an example
User
22/11/2017 - 09:21
and i want it to match the whole string not just the first character
Excelchat Expert
22/11/2017 - 09:22
Are you getting your values from the array?
User
22/11/2017 - 09:22
yes
Excelchat Expert
22/11/2017 - 09:22
if rngArr(i, j) <> cell.value
User
22/11/2017 - 09:23
ok
User
22/11/2017 - 09:23
i will try
User
22/11/2017 - 09:25
what if the cells are in Sheet1?
Excelchat Expert
22/11/2017 - 09:26
set rng=Sheet1.range("A1:A107")
User
22/11/2017 - 09:28
rng As Range
User
22/11/2017 - 09:28
duplicate declaration in scope
User
22/11/2017 - 09:28
how can i user another name
Excelchat Expert
22/11/2017 - 09:29
rng is just a variable, you can replace with anything you want;
Excelchat Expert
22/11/2017 - 09:29
dim myRange as Range
Excelchat Expert
22/11/2017 - 09:29
set myRange=Sheet1.Range("A1:A107")
User
22/11/2017 - 09:30
okay
User
22/11/2017 - 09:31
invalid Next Control variable reference
User
22/11/2017 - 09:31
NExt j
Excelchat Expert
22/11/2017 - 09:31
send me code so I check
User
22/11/2017 - 09:33
Dim cnt As Long
Function viceversa(lkup As Variant, rng As Range, nmbr As Long, Optional rec As Boolean) As String
Dim rngArr
Dim temp As String
Dim rng2 As Range
Set rng2 = Sheet1.Range("A1:A107")
Dim cell As Range
If rec = False Then cnt = 1
rngArr = rng.Value
For i = LBound(rngArr, 1) To UBound(rngArr, 1)
If rngArr(i, 1) = lkup Then
For j = LBound(rngArr, 2) + 1 To UBound(rngArr, 2)
For Each cell In rng2
If rngArr(i, j) <> cell.Value Then
If cnt = nmbr Then
viceversa = rngArr(i, j)
Exit Function
Else
cnt = cnt + 1
End If
Else
temp = viceversa(rngArr(i, j), rng, nmbr, True)
If temp <> "" Then
viceversa = temp
Exit Function
End If
End If
Next j
End If
Next i
viceversa = ""
End Function
User
22/11/2017 - 09:33
https://stackoverflow.com/questions/34422994/how-to-list-ingredients-for-a-product-if-one-product-can-have-another-product-as
User
22/11/2017 - 09:33
i am doing this link
Excelchat Expert
22/11/2017 - 09:37
try this
Excelchat Expert
22/11/2017 - 09:37
Dim cnt As Long
Function viceversa(lkup As Variant, rng As Range, nmbr As Long, Optional rec As Boolean) As String
Dim rngArr Dim temp As String
Dim rng2 As Range
Set rng2 = Sheet1.Range("A1:A107")
Dim cell As Range
If rec = False Then
cnt = 1 rngArr = rng.Value
For i = LBound(rngArr, 1) To UBound(rngArr, 1)
If rngArr(i, 1) = lkup Then
For j = LBound(rngArr, 2) + 1 To UBound(rngArr, 2)
For Each cell In rng2
If rngArr(i, j) <> cell.Value Then
If cnt = nmbr Then
viceversa = rngArr(i, j)
Exit Function
Else
cnt = cnt + 1
End If
Else
temp = viceversa(rngArr(i, j), rng, nmbr, True)
If temp <> "" Then
viceversa = temp
Exit Function
End If
End If
next cell
Next j
End If
Next i
viceversa = ""
End Function
User
22/11/2017 - 09:38
can you tell me what you changed becuase i could not copy and paste it
Excelchat Expert
22/11/2017 - 09:38
before the next j, I added
Excelchat Expert
22/11/2017 - 09:38
next cell
User
22/11/2017 - 09:40
the code worked but it did not do what i want
User
22/11/2017 - 09:40
and i am unable to attach it to you
Excelchat Expert
22/11/2017 - 09:40
You cannot attach macro file. You have to put it like on google drive or dropbox and share the link
Excelchat Expert
22/11/2017 - 09:41
what do you want it to do
Excelchat Expert
22/11/2017 - 09:41
?
User
22/11/2017 - 09:42
https://drive.google.com/file/d/1CmdDjfLVCWYpMhcD2ppBewFa1ZqM7ZCd/view?usp=sharing
User
22/11/2017 - 09:42
here it is
User
22/11/2017 - 09:43
i word for a restauart, he have this excel, he wants to know each product what ingredients it have, in details, because some ingredients are products that have ingredients, we want all to be ingredients
User
22/11/2017 - 09:45
Function viceversa(lkup As Variant, rng As Range, nmbr As Long, Optional rec As Boolean) As String
Dim rngArr
Dim temp As String
If rec = False Then cnt = 1
rngArr = rng.Value
For i = LBound(rngArr, 1) To UBound(rngArr, 1)
If rngArr(i, 1) = lkup Then
For j = LBound(rngArr, 2) + 1 To UBound(rngArr, 2)
If Left(rngArr(i, j), 1) <> "P" Then
If cnt = nmbr Then
viceversa = rngArr(i, j)
Exit Function
Else
cnt = cnt + 1
End If
Else
temp = viceversa(rngArr(i, j), rng, nmbr, True)
If temp <> "" Then
viceversa = temp
Exit Function
End If
End If
Next j
End If
Next i
viceversa = ""
End Function
User
22/11/2017 - 09:45
this code was returning the names as is, and not fetching if the name is a product about its dependent ingredients to write them also
Excelchat Expert
22/11/2017 - 09:50
You want it to also show Yeast Raised Concentrate, Water, etc from B2 going down on sheet3
User
22/11/2017 - 09:51
for example for Yeast mix it should show me
User
22/11/2017 - 09:51
Plain Flour Yeast Raised Concentrate Water Dry Yeast Pack*450 Gr Soya Oil
Excelchat Expert
22/11/2017 - 09:51
It should show below Yeast?
Excelchat Expert
22/11/2017 - 09:52
Yeast Mix
User
22/11/2017 - 09:52
yes
User
22/11/2017 - 09:52
for L1 in sheet3, that only contains yeast , i dont want to see yeast, i want it to show me also
User
22/11/2017 - 09:52
Plain Flour Yeast Raised Concentrate Water Dry Yeast Pack*450 Gr Soya Oil
User
22/11/2017 - 09:52
the ingredients of yeast
User
22/11/2017 - 09:53
so in summary if it have ingredients i want them, if ingredients and products i want them all as ingredients
User
22/11/2017 - 10:01
is it possible?
Excelchat Expert
22/11/2017 - 10:02
Yes but takes a bit of time, still looking at the code
User
22/11/2017 - 10:02
oh okay, the last code i sent you is working almost 50% the trick is
User
22/11/2017 - 10:03
One thing that will need to change is the test of whether it is a product or not. So change this line If Left(rngArr(i, j), 1) <> "P" Then to something that works to denote that the ingredient being tested is actually a product.
Excelchat Expert
22/11/2017 - 10:04
You will need to repost, not enough time left
User
22/11/2017 - 10:07
oh
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
