Create Complex Polygon - Determine If Coords Intersect
Dec 5, 2007
I am creating an automatic, mobile logging system utilizing a GPS. The US Census Bureau provides approximate coordinates for each US zipcode. An example of "45252", which is my home zipcode is below...
-84.629861342656539.2773019382716
-84.630347000000039.3120560000000
-84.629424000000039.3104500000000
-84.633324000000039.3090500000000
-84.634224000000039.3091500000000
-84.637248000000039.3062570000000
-84.627748000000039.3043570000000
-84.621147000000039.3024570000000
-84.618347000000039.3001570000000
-84.611047000000039.2924570000000
These sets of Longitute and Latitude coords define a boundary for my zip code. What I need to do is create a complex polygon with the above coords and then, feeding a function a Longitute and Latitude, determine if that given global position falls within my polygon (zip code boundary).
For example:
Longitude: -84.6197
Latitude: 39.2947
Will fall within the above polygon and my function would return true.
View 9 Replies
ADVERTISEMENT
Nov 1, 2013
I have two ranges(range1 and range2) and i would like to create a third range(range3 that i have defined already) based on the values that dont intersect between range1 and range2. Below is what i have so far.
Sub runthrough()
Dim rng As Range
Range("range3").Clear
[Code]....
View 3 Replies
View Related
Aug 15, 2008
I have a workbook containing a lot of worksheets, each sheet contains approx 60,000 rows of data (each sheet varies in the number of rows slightly but the data is always contained in column A). There are only 4 unique values and my task is to filter out the 4 seperate values and copy & append them to a master list in 4 columns side-by-side i.e. valueA in A1, valueB in B2 etc.
The problem I have run into is that when I have filtered out one of the values and try to copy it I get an error message - "Excel cannot create or use the data range reference because it is too complex".
This has left me thinking that the only way to process the data is to manually copy approx 20,000 rows at a time (I'm not sure what the maximum amount of filtered data Excel can handle is but this it can definitely handle) and paste to the master list.
View 9 Replies
View Related
Jul 3, 2014
it doesn't like the fact I have split column headers and I was wondering if there was a way around it or another solution to save me time manually counting or filtering. I can't attach the actual spreadsheet for some reason but attach a screenshot to illustrate what I mean.
I need to count the number of 1's, 2's, 3's etc in each 'On Exit' column for each area. I then need to work out what percentage of the cohort they each are.
View 9 Replies
View Related
Nov 12, 2009
My app uses Worksheet_Change(ByVal, Target as Excel.Range). To narrow the scope of the event I then use either:
View 2 Replies
View Related
Jan 6, 2007
I've been working for awhile now on vector-based geometry/trig and so forth. I'm very interested now in developing a solution to the following:
Given pixel coordinates for an n-sided polygon (concave or convex) calculate the pixel area of the polygon.
My problem is, I don't have time with my reg'lar job/duties to investigate this and find the solution quicly. I was hoping perhaps some of you smarter-than-the-average bunch may already know the answer and could bring it to me a little quicker.
If no one should respond, I'll let you know when (or should I say "if") I figure it out... but unfortunately, it might be a few weeks before I can get to it.
View 9 Replies
View Related
Sep 7, 2012
Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.
original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}
New formulas needed or revised from the above:
Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)
Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"
Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.
Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)
[Code] .........
View 1 Replies
View Related
May 30, 2014
I have to draw a polygon of n sides whose , whose length of side will be given in the cells of excel and the diagram should be displayed in excel itself automatically
Like I have A1=5 A2=5 B1=4 B2=3 C4=5
Then diagram should be like in the attachment : Untitled5.png
View 2 Replies
View Related
Sep 7, 2009
Given a pivot table, is there any way that I can determine the source data that was used to create the pivot table?
I suspect that the pivot table was created using only part of the data, but I'm not sure.
View 9 Replies
View Related
Aug 29, 2012
I am trying to create a formula that will determine the number of orders for a specific customer whose orders have a pallet count between a certain range. The customer code is a cell reference, as well as the beginning and ending values I am looking for. I have the data on a separate tab from where I am putting the formula.
Here is what I was trying to use, but it was coming up with "0" for everything:
=COUNTIFS(Data!$I:$I,E7,Data!$I:$I,">=A8",Data!$I:$I,"
View 1 Replies
View Related
Jan 29, 2008
Im tring to run the procedure called KeyEventsOn ONLY when a single cell in range E29:E30 changes.
The Intersect and Target are borrowed from a book , I dont fully understand how these work. I am assuming that 'Target' should simply reflect the currently selected range, and the 'intersect' function runs a check as to whether the target is in the range to be checked.
The problem Im having is the variable 'Target'. If the above is true, why for example when I type 'ABC' into the current selected cell does the variable 'Target' show as "ABC" and not "$E$29" (or whatever cell is selected)...
View 9 Replies
View Related
Jul 7, 2006
I'm having some trouble fully understanding the Intersect Method. The help files use examples that check if a range Intersects another, if the do then display a message box; I think I understand that.
The usage I don't understand is like this
Set MyRange = Range("C:C", "D:D", "E:E")
Code here
Intersect(CopyRange, MyRange).copy
View 4 Replies
View Related
Jan 11, 2010
When trying to intersect ot ranges in an application it selects all ranges example:
View 2 Replies
View Related
Dec 19, 2013
Got a series of data in a master sheet. I wondered is there a formula that will find an entry in column A, find another entry in row 1 and return the figure where the two meet.
View 2 Replies
View Related
Jan 30, 2009
I have a piece of code that changes the format of a cell using an intersect statement on the range in question. However, if I try and select more than one cell in the range then an error message is returned: 'Run Time Error 13': Type mismatch. I know that it is caused by selectin multiple cells - is there a way to error handle this so that, when multiple cells are selected, it ignores the intersect statement?
View 2 Replies
View Related
Nov 11, 2011
I have a Worksheet Change event that when a cell (H5) is changed, it does a search for the value on another sheet. When it finds that value, I would like it to find the intersection of the column it found the value in and a named range.
Here is what I have so far. It works when I use the
Code:
.Range("H4") = c.Offset(15)
, but not the
Code:
.Range("S8") = isect.Offset(3)
.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
UpperCase Target
'Fills in 60 Point form when part number is changed
'Sheets("60 POINT").Unprotect
[Code] ........
View 9 Replies
View Related
Jan 12, 2007
The match results are posted on another sheet and the winner allocated the letter W and the loser allocated L (in the sample workbook attached these are on sheet2 cells B1 and B2 with the winners name in A1 and the losers in A2, i.e. Bill and Susan) I have the code for this working properly.
What I need to do next is transfer the W and L onto the grid in sheet1 and insert the W in the cell that is the intersection between the row with the winners name and the column with the losers name. Then insert the L in the cell that is the intersection between the row with the losers name and column with the winners name. I hope that makes sense!
If it is simpler to insert the W and L into the grid directly from the winner and loser names in sheet2 col A rather than allocating the W and L in col B first, then that is great.
View 9 Replies
View Related
Feb 19, 2007
I have 4 sheets (2006,2007,2008,2009) Each has appx. 365 named ranges of 65 rows each. Each row (col A) has text(7:00 am, 7:15, 730, 7:45...11:00 pm (thats 65 rows)) to make a very useful schedule. The top 8 rows of each sheet are frozen so that a control panel has a place to sit. From the control panel, you can click on the go to "TODAY" button and the schedule scrolls to that day and indicates it in a label on the panel. When I named each range, I put a water mark of the day in the back ground so the user always knows what day it is. I also named each range with its respective date and the label on the control panel is supposed to indicate the active range(for instance: the user scrolls down 12 days and clicks inside it).
MY PROBLEM: Code that returns the intersection of the active cell and the name of the range does not work with the way in which I named my ranges. It works on a more simple named range However.
Sub DefineNameandwatermark2007()
Dim i As Long
Dim a, b, c, X
Dim dt As Date
Dim sr As String
dt = DateSerial(2007, 1, 1)
For i = 8 To 25000 Step 66
j = j + 1
a = Application.WorksheetFunction.Text(dt + j, "ddd")
b = Application.WorksheetFunction.Text(dt + j, "mmm")
c = Application.WorksheetFunction.Text(dt + j, "d")
X = a & "_" & b & "_" & c
ActiveWorkbook.Names.Add Name:=X, RefersToR1C1:="=2007!R" & i & "C1:R" & _
i + 65 & "C1"....................................
View 2 Replies
View Related
Oct 2, 2004
If I have a graph with 2 series of data plotted, how can I display the x-axis' value when these 2 curves intersect each other?
View 9 Replies
View Related
Jan 18, 2009
I am trying to clear the contents in a rows reference by a variable (rownum) using the
View 2 Replies
View Related
May 13, 2007
This is my
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then
If Intersect(Target, Range("F6:F6000")) Is Nothing Or Intersect(Target, Range("F6:F3000")) = "" Then
If Intersect(Target, Range("I6:I6000")) Is Nothing Or Intersect(Target, Range("I6:I6000")) = "" Then
Exit Sub
Else
Do Something......
End If
End If
End If
End Sub
when excution comes to this line
If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then
a run time error (91) appears, it tells object variable or with block variable not set.
View 9 Replies
View Related
May 19, 2007
item = InputBox("Please Select Row Number of Child to be Removed")
myString1 = Range("$a" & item & ":$c" & item, "$e" & item & ":$j" & item).Select
I copied this code from a prior worksheet and the range was not split (i.e. just "$a" & item & ":$c" & item). When I try to make the range selection grab two separate areas, I get the above listed error. Do I not have the quotations right? I have tried them several different ways. Not sure what else to try.
View 3 Replies
View Related
Jul 5, 2014
In my "cover" sheet I click the allocate cover button and it then generates a list of data below the first table. This generated data now needs to update the "timetable" sheet by using the "allocated cover teacher" and "period" cells as a reference to then input the class code into the timetable at this time.
cover.jpg
So in this picture we see that Dorothy*Schultz (in cell E27) has been given a class 3 MABA RM 15(cell D27) on TuesdayP1 (C27). I want search for Dorothy*Schultz and TuesdayP1 in the "timetable" tab to input the name of the class which in this instance would be input to cell I20 in the "timetable tab".
VBA COVER Forum Version 1.xls
View 10 Replies
View Related
Mar 15, 2008
i have this formula that is complex ( atleast it is to me ) and i need to add more to it. Here is the formula:
=VLOOKUP(" "&LEFT($A5,FIND("[",$A5)-2),'7E'!$C$2:$D$682,2,FALSE)*0.99
What i am needing is, when it gets the price from another worksheet, if that price is below 0.04 that it makes it 0.05
View 9 Replies
View Related
Jul 29, 2014
I wish to make a transposition using dictionary. I wanted to take a script jindon, but I have difficulties to adapt.
VB:
Option Explicit
Sub test()
Dim a, i As Long, ii As Long, w, rng As Range, cpt As Byte
a = Cells(1).CurrentRegion.Value
[Code] .....
View 9 Replies
View Related
Apr 22, 2008
The formula below works apart from the first bit which i want to be if F23 says 'fail' then i want the cell to say 'no dissertation' but if f23 says pass then i want the formula past that bit to be put into action.
=IF(F23="Fail",0)*IF(COUNTIF(B22:B45,"Dissertation")=1,IF(VLOOKUP("Dissertation",B22:C45,2,0)>39,LOO KUP(F26,{0,40,50,60,70},{"No Dissertation","3rd","2:2","2:1","1st"}),"No Dissertation"),"No Dissertation")
isolation123
View 13 Replies
View Related
Feb 25, 2009
I am an experienced Excel user, but it does not seem possible for this formula to be written all within one single cell. The SUM combines rounding a known value, followed by calculations, and subtracting a rounded number with modified calculations.
This is the formula which does work, although I would prefer for the sake of accuracy (and succeeding in the task of making the formula do exactly what I wanted it to do!!):
=ROUND(SUM(T9/1.5)/5,0)*(5)*(6.5%)+(26.25+15.81+1)+SUM((T9/1.5)*(6.5%)+26.25+15.81+1)*19%
The value of T9 is 2283.3 recurring.
There are two main parts to the SUM:
=ROUND(SUM(T9/1.5)/5,0)*(5)*(6.5%)+(26.25+15.81+1)
...and:
+SUM((T9/1.5)*(6.5%)+26.25+15.81+1)*19%
The challenge is to insert another:
=ROUND(SUM(T9/1.5)/5,0)*(5)
in replacement of:
=SUM((T9/1.5)
I'm aware that the use of brackets differs between the two sections of the sum, but it is neccessary to allow the correct answer.
Is this too much for one poor cell to handle?
View 8 Replies
View Related
Jul 21, 2014
I am creating a complex formula with UDF, this Hlookup is part of my complete formula. Meanwhile, I am working on this piece, not Sure what I am doing Wrong
I try to conver "MonthCurrent" Variable in a Year because the MonthCurrent is 06/01/2014, so will be Year(6/1/2014) = 2014
My Range called "DailyRates" is based per Year = 2014, 2015 ......
[Code]......
View 2 Replies
View Related
Feb 3, 2009
The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report.
Then, I need the following logic as VB macro.
for row = 6
F6 = (G6*G4) + (H6*H4) + (I6*I4)+...+ ("max_col:6" * "max_col:4") / (G4+H4+I4... "max_col:4")
View 2 Replies
View Related
Jan 14, 2010
The function LOGEST fits to the equation y=b*m^x.
Does anyone know if more complex functions can be found in Excel?
Ideally I would like to fit to y=b*m^(x-a) + c. If anyone has come across a method, or some code, which allows this.
View 14 Replies
View Related
