Re: date range dependent on current date
I'm not sure you're context, your message reads confusing, but here's an example of comparing the current date with the date in another cell. In this case, if the current date is greater than the date in cell A1, then it displays the note, otherwise, no note.
Date Compare Formula
| Here's your note |
| Spreadsheet Formulas |
Re: Excel Template Location
I found this. Seeing that I don't know much about a Mac, I'll have to trust that Microsoft knows more than I do. 
Re: Excel Template Location
Quote from Leonard Lutz;659010Thank you for the reply. I should have mentioned that I'm using Excel for Mac.
Sorry, can't help you there. I'm a PC, a Mac is an Apple and PC's eat apples, not play with them. hehe Just kidding around, I'll have to see what I can find about where Office for Mac stores things. I'm already late going home, so it'll have to be tomorrow.
Re: Dynamic Range to define Excel Solver parameters that should change
The easiest way is just to determine the last row... or in this case, the last row down from H2 containing data.
Dim lr As Long
lr = Range("H2").End(xlDown).RowThen use the last row to pass to the byChange like so
ByChange:="$H$2:$H$" & lr,
Re: Data from Matrix to Rows
What was your solution? It could help others trying to figure out the same thing.... Thanks.
Re: Excel Template Location
On my system they are located here....
C:\Documents and Settings\LocalAdmin\Application Data\Microsoft\Templates
However, on your system they could be located under a different user, so look under all folders under Documents and Settings, then continue looking for Application Data\Microsoft\Templates
Keep in mind that Application Data is a hidden folder, which is probably why the Search is not finding it. Make sure your system is set to "Show hidden files and folders" or you will not be able to find it either.
Re: VBA code to identify non matching text
I was playing with this some more today and thought you might find it more useful as a function.
Public Function RevDiffStr(string1 As String, string2 As String, First As Boolean)
Dim StringComp1Arr As Variant
Dim stringComp2Arr As Variant
Dim i As Long
Dim j As Long
Dim OutputArr() As Variant
Dim OutputSTR As String
Dim DiffFnd As Boolean
'Split string1 into Array
ReDim StringComp1Arr(Len(string1) - 1)
For i = 1 To Len(string1)
StringComp1Arr(i - 1) = Mid$(string1, i, 1)
Next
'Split string2 into Array
ReDim stringComp2Arr(Len(string2) - 1)
For i = 1 To Len(string2)
stringComp2Arr(i - 1) = Mid$(string2, i, 1)
Next
DiffFnd = False
'change to less than symbol on the following line to return
'the differences in the opposite string
If First Then
If Len(string1) < Len(string2) Then
For i = LBound(StringComp1Arr) To UBound(StringComp1Arr)
If StringComp1Arr(i) <> stringComp2Arr(i) Then
For j = i To UBound(stringComp2Arr)
ReDim Preserve OutputArr(j)
OutputArr(j) = stringComp2Arr(j)
Next j
OutputSTR = Join(OutputArr, "")
DiffFnd = True
Exit For
End If
Next i
Else
For i = LBound(stringComp2Arr) To UBound(stringComp2Arr)
If stringComp2Arr(i) <> StringComp1Arr(i) Then
For j = i To UBound(StringComp1Arr)
ReDim Preserve OutputArr(j)
OutputArr(j) = StringComp1Arr(j)
Next j
OutputSTR = Join(OutputArr, "")
DiffFnd = True
Exit For
End If
Next i
End If
Else
If Len(string1) > Len(string2) Then
For i = LBound(StringComp1Arr) To UBound(StringComp1Arr)
If StringComp1Arr(i) <> stringComp2Arr(i) Then
For j = i To UBound(stringComp2Arr)
ReDim Preserve OutputArr(j)
OutputArr(j) = stringComp2Arr(j)
Next j
OutputSTR = Join(OutputArr, "")
DiffFnd = True
Exit For
End If
Next i
Else
For i = LBound(stringComp2Arr) To UBound(stringComp2Arr)
If stringComp2Arr(i) <> StringComp1Arr(i) Then
For j = i To UBound(StringComp1Arr)
ReDim Preserve OutputArr(j)
OutputArr(j) = StringComp1Arr(j)
Next j
OutputSTR = Join(OutputArr, "")
DiffFnd = True
Exit For
End If
Next i
End If
End If
If DiffFnd = False Then
OutputSTR = "No Differences"
End If
RevDiffStr = OutputSTR
End FunctionYou can use it in a formula like this.
Cell A1: I am a boy
Cell A2: I m a boy
Any cell: =RevDiffStr(A1, A2, True)
Would produce result: "am a boy"
Any cell: =RevDiffStr(A1, A2, False)
Would produce result: "m a boy"
The true or false is to determine which of the two strings to produce the result from. True uses the first string, False uses the second string.
or you could also use it in other vba code like this....
Sub TestStrResults()
Dim mySTRvar1 As String
Dim mySTRvar2 As String
Dim result As String
mySTRvar1 = "I am a boy"
mySTRvar2 = "I m a boy"
result = RevDiffStr(mySTRvar1, mySTRvar2, False)
MsgBox result
End SubCheers,
Peixe
Re: VBA code to identify non matching text
Quote from Deino;657516
Cell A1 = I am a boy
Cell A2 = I m a boy
I am looking for a code that identifies and displays in another cell or highlights "m a boy" is the text that is not matching.
This may not be pretty, but it works as described. Starting with strings in A1 and A2, this will put the result in B3.
Sub ReverseDiffStringCompare()
Dim string1 As String
Dim string2 As String
Dim StringComp1Arr As Variant
Dim stringComp2Arr As Variant
Dim i As Long
Dim j As Long
Dim OutputArr() As Variant
Dim OutputSTR As String
Dim DiffFnd As Boolean
string1 = Cells(1, "A").Value
string2 = Cells(2, "A").Value
'Split string1 into Array
ReDim StringComp1Arr(Len(string1) - 1)
For i = 1 To Len(string1)
StringComp1Arr(i - 1) = Mid$(string1, i, 1)
Next
'Split string2 into Array
ReDim stringComp2Arr(Len(string2) - 1)
For i = 1 To Len(string2)
stringComp2Arr(i - 1) = Mid$(string2, i, 1)
Next
DiffFnd = False
'change to less than symbol on the following line to return
'the differences in the opposite string
If Len(string1) > Len(string2) Then
For i = LBound(StringComp1Arr) To UBound(StringComp1Arr)
If StringComp1Arr(i) <> stringComp2Arr(i) Then
For j = i To UBound(stringComp2Arr)
ReDim Preserve OutputArr(j)
OutputArr(j) = stringComp2Arr(j)
Next j
OutputSTR = Join(OutputArr, "")
DiffFnd = True
Exit For
End If
Next i
Else
For i = LBound(stringComp2Arr) To UBound(stringComp2Arr)
If stringComp2Arr(i) <> StringComp1Arr(i) Then
For j = i To UBound(StringComp1Arr)
ReDim Preserve OutputArr(j)
OutputArr(j) = StringComp1Arr(j)
Next j
OutputSTR = Join(OutputArr, "")
DiffFnd = True
Exit For
End If
Next i
End If
If DiffFnd = False Then
OutputSTR = "No Differences"
End If
Cells(3, "B") = OutputSTR
End SubRe: VBA and Tablet Orientation
and here I thought I was just going to post something brilliant because I got this to work. LOL I'll post it anyway, but your way is probably better.....
Using the vb6 initialize code as posted previously, but calling the form in the following fashion.
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Function IsUserFormLoaded(ByVal UFName As String) As Boolean
Dim UForm As Object
IsUserFormLoaded = False
For Each UForm In VBA.UserForms
If UForm.Name = UFName Then
IsUserFormLoaded = True
Exit For
End If
Next
End Function
Sub showform2()
Dim CurrentScreenWidth As Integer
Dim OnLoadScreenWidth As Integer
OnLoadScreenWidth = Application.Width
'load userform and continue to run code
UserForm1.Show False
'Allows Userform action while still running code
DoEvents
Do While IsUserFormLoaded("UserForm1") = True
'Sleep for 200 milliseconds
Sleep 200
DoEvents
'Check screen width when loaded against current screen width
CurrentScreenWidth = Application.Width
If CurrentScreenWidth <> OnLoadScreenWidth Then
'Unloads and reloads userform, however, this could be used
'to change the size of the userform instead. You would have to
'reset the OnLoadScreenWidth along with the change.
Unload UserForm1
UserForm1.Show False
DoEvents
End If
Loop
End SubRe: VBA and Tablet Orientation
This doesn't work the way it looks like it would work, but I thought I'd post it to maybe spark some more thought on the subject. The intent being that it would load the form, and while the form is loaded, every 200 milliseconds it would check the orientation while the form was loaded. However, it did not work as intended and never fully displayed my form. If there was a way to truly run code in the background, then something like this might work and let the form do its job. In this case, I just unloaded and reloaded the form, however, you'd probably just want it to do your form resizing. That is, if we can figure out a way to make it work in the background.
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Function FormIsLoaded(UFName As String) As Boolean
Dim UF As Integer
For UF = 0 To VBA.UserForms.Count - 1
FormIsLoaded = UserForms(UF).Name = UFName
If FormIsLoaded Then Exit Function
Next UF
End Function
Sub showform()
Dim CurrentScreenWidth As Integer
Dim OnLoadScreenWidth As Integer
OnLoadScreenWidth = Application.Width
'load userform and continue to run code
UserForm1.Show False
Do While FormIsLoaded(UserForm1.Name) = True
'sleep for 200 milliseconds
Sleep 200
CurrentScreenWidth = Application.Width
If CurrentScreenWidth <> OnLoadScreenWidth Then
Unload UserForm1
UserForm1.Show False
End If
Loop
End SubRe: VBA and Tablet Orientation
No, even the vb.net one was an onLoad event.
Private Sub UserForm1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)I'm sure that anything else than this would require API calls and checks every so many milliseconds, which would be far beyond my expertise.
Re: VBA and Tablet Orientation
Rather than using the vb.net code, I used the vb6 code provided further down the same page. http://msdn.microsoft.com/en-us/library/ms812142.aspx
With a minor tweak, I got it to work. Although, I do not have a tablet, so you'll have to test it to see if it works. The way it looks, it will work if you load the form, but will not change if the form is already loaded.
Private Sub UserForm_Initialize()
'Form initialize routine
Dim theScreenWidth As Integer
Dim theScreenHeight As Integer
Dim iReturn As Integer
theScreenWidth = Application.Width
theScreenHeight = Application.Height
If (theScreenWidth > theScreenHeight) Then
'Run the application in landscape, as in:
iReturn = MsgBox("Run in landscape.", vbOKOnly, "Landscape")
Else
'Run the application in portrait, as in:
iReturn = MsgBox("Run in portrait.", vbOKOnly, "Portrait")
End If
End SubRe: Run SAME code for different ranges using BeforeDoubleClick event
Quote from Musoguy;657749
Peixe, that worked great. I'd actually attempted to do that, but had placed quotation marks around each named range, not just one set around all three so it didn't work.
When working with multiple range, all ranges should be encompassed within a single set of double quotes.
For example:
To cover A1 thru A10, B2 and C1 thru C10 it would look like this.
Range("A1:A10, B2, C1:C10")
that would cover everything. If add quotes and commas outside of the quotes then you can only have a starting and ending cell range.
IE: the range A1 thru A10 would be like this
Range("A1","A10")
This is the same as saying Range("A1:A10") except you are limited to only that range. Whereas including everything within a single set of double quotes, you can have many multiple ranges covered within a single range statement.
The same apparently applies to named ranges. I don't use them, so I just took a logical guess.
Re: Run SAME code for different ranges using BeforeDoubleClick event
Quote from Musoguy;657733However I also need the code to work on two other named ranges "Checkboxes2" (E4:E100) and "Checkboxes3" is G4:G100.
Hoping someone can help
I don't work with named ranges, so this is untested, but in theory, you should be able to include them all as one range. It's the same as you would without naming the ranges.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("Checkboxes1, Checkboxes2, Checkboxes3")) Is Nothing Then Exit Sub
Target.Font.Name = "marlett"
If Target.Value <> "a" Then
Target.Value = "a"
Cancel = True
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents
Cancel = True
Exit Sub
End If
End SubRe: Sort entire table eachtime after entering new data record at the end of the list
A simple way to do it is when the last row of column AD is updated, then sort. You could also add in validation to ensure all columns were filled in.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c1 As Range
Dim c2 As Range
Dim rng As Range
Dim lr As Long
lr = Range("AD65536").End(xlUp).Row
If Not Intersect(Target, Range("AD" & lr)) Is Nothing Then
'sort
Set c1 = Cells(7, "A")
Set c2 = Cells(lr, "AD")
Set rng = Range(c1, c2)
rng.sort Key1:=Range("B7"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
End SubRe: Pull Data from sheet 2 and 3 to sheet 1 if data in column a matches
What is the name of the 3rd sheet? Is it just Sheet4?
As your vendors grow, this method can quickly become obsolete. You can only nest 7 IF statements I believe, so it might be better to merge vendors into one sheet with the ability pick out a single vendor.
Let me know what the new sheet name is and I can update the formula or if you want to consider another route, that's ok too.
Re: Pull Data from sheet 2 and 3 to sheet 1 if data in column a matches
I can get you half way there. This formula works for row 2, just paste to row 2 and copy down.....
=IF(ISNA(VLOOKUP(A2,Sheet3!$A$2:$B$26,2,FALSE)),VLOOKUP(A2, Sheet2!$A$2:$C$82, 3, FALSE),VLOOKUP(A2,Sheet3!$A$2:$B$26,2,FALSE))Its only downfall is if the part number isn't in either sheet2 or sheet3 it'll give you #N/A.
I was trying to account for N/A on sheet3 also, but kept getting "too many arguments" and ran out of time to figure out why as it's time for me to go home, but at least you're half way there.
Edit:
Couldn't leave with something unfinished, so here's a formula that will work all around.
=IF(ISNA(VLOOKUP(A2,Sheet3!$A$2:$B$26,2,FALSE)),IF(ISNA(VLOOKUP(A2, Sheet2!$A$2:$G$82, 7, FALSE)), "", VLOOKUP(A2, Sheet2!$A$2:$G82, 7, FALSE)),VLOOKUP(A2,Sheet3!$A$2:$B$26,2,FALSE))