Re: Looping constants
I apologize. Sometimes I confuse myself too. lol. But I think you understood what I meant. This is what I'm actually looking for. Thanks a lot!!!
Re: Looping constants
I apologize. Sometimes I confuse myself too. lol. But I think you understood what I meant. This is what I'm actually looking for. Thanks a lot!!!
Hi. I need your experties guys. Is it possible to create 2 of that enclosed letter in one string?
val(1)(1) = "Dog1"
val(2)(1) = "Cat1"
val(1)(2) = "Dog2"
val(2)(2) = "Cat2"
i = 1
j = 2
Debug.Print val(i)(j)
Display More
RESULT: "dog2"
Reason for the need is, i have per line inputs that needs to be calculated each per year. I am calculating each in one module (publicly declared) and then call the specific string in another module.
example:
I have a sheet with this field...
| #Value | #Contract | #Qty |
I created a module to calculate each line's cost per year...
public Value_Yr(1) as variant
public Value_Yr(2) as variant
public Value_Yr(3) as variant
sub Calc_val()
for each c in range("tablename")
for i = 1 to 5
value_Yr(i) = Value_Yr(i) + (#Value * #Qty)
next i
next c
end sub
Display More
If i need to return the value of, say, year 2, i would just call it in another module like...
Now, I realized i need the value of "per line" per year. I don't wanna flood the module with too many calculations for each line and would just like to loop it just like how i looped it for yearly values. What should be the approach? What I was thinking is like below, but ofcourse it won't work.
public Value_Yr(1)_Line(1) as variant
public Value_Yr(1)_Line(2) as variant
public Value_Yr(2)_Line(1) as variant
public Value_Yr(2)_Line(2) as variant
sub Calc_val()
for each c in range("tablename")
j = c.row
for i = 1 to 5
value_Yr(i)_Line(j) = Value_Yr(i)_Line(j) + (#Value * #Qty)
next i
next c
end sub
Display More
Thank you in advance.
Re: Match Values with Greater/Less Than Condition
my bad. will take note
Re: Match Values with Greater/Less Than Condition
Return ID 125 because it meets the criteria
Re: Match Values with Greater/Less Than Condition
Hi Krishnakumar,
The result would show the ID that has the same exact value (dog, cat) which also has a conflict with dates.
Re: Match Values with Greater/Less Than Condition
I think i got the formula:
[f]=IFERROR(INDEX($A$4:$A$6,MATCH(B2&E2&F2&G2&H2&I2&J2&"TRUETRUETRUEFALSE",$B$4:$B$6&$E$4:$E$6&$F$4:$F$6&$G$4:$G$6&$H$4:$H$6&$I$4:$I$6&$J$4:$J$6&($K$4:$K$6<=K2)&($L$4:$L$6>=K2)&($K$4:$K$6<=L2)&($L$4:$L$6>=L2),0)),INDEX($A$4:$A$6,MATCH(B2&E2&F2&G2&H2&I2&J2&"FALSETRUETRUETRUE",$B$4:$B$6&$E$4:$E$6&$F$4:$F$6&$G$4:$G$6&$H$4:$H$6&$I$4:$I$6&$J$4:$J$6&($K$4:$K$6<=K2)&($L$4:$L$6>=K2)&($K$4:$K$6<=L2)&($L$4:$L$6>=L2),0)))[/f]
will revert if i got the correct one
Re: Match Values with Greater/Less Than Condition
Hi Krishnakumar,
Thanks for the quick response. However, the result of the formula is #N/A. I don't understand why
Hello everyone,
I need help with a function to find the same exact value wherein it includes a condition that a date conflicts with the one I am searching.
[TABLE="class: grid, width: 400"]
[tr][td]ID #
[/td][td]value1
[/td][td]value2
[/td][td]date1
[/td][td]date2
[/td][td]notes
[/td][/tr][tr][td]ID 123
[/td][td]DOG
[/td][td]CAT
[/td][td]5
[/td][td]10
[/td][td]reference
[/td][/tr][tr][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]ID 126
[/td][td]CAT
[/td][td]DOG
[/td][td]11
[/td][td]15
[/td][td]different value
[/td][/tr][tr][td]ID 127
[/td][td]DOG
[/td][td]CAT
[/td][td]11
[/td][td]15
[/td][td]same value but date doesn't conflict
[/td][/tr][tr][td]ID 125
[/td][td]DOG
[/td][td]CAT
[/td][td]9
[/td][td]15
[/td][td]same value and date conflicts
[/td][/tr][tr][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/TABLE]
Therefore: Return ID 125 because it meets the criteria
my formula for match is below:
formula for conflicting dates is below:
where:
A1 = date1
A2 = searched date1
B1 = date2
B2 = searched date2
What I'm trying to accomplish here is to combine those 2 functions into one. Thanks in advance!