I am trying to find the first empty cell in a row using FIND or MATCH, preferably MATCH. Below are the various ways I have attempted to write the code. in most of the examples I have tried either "" or " " to search. Also with using Find I have tried the search direction of xlprevious and xlnext. The different variations result in finding the first cell in the range, which contains text. Or it does not find an empty cell given the result is 0.
CID = Sheet1.Range("1:1").Find(" ", LookAt:=xlWhole)
CID = Sheet1.Range("1:1").Find("", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
CID = Sheet1.Range("1:1").Find("", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Row '
CID = WorksheetFunction.Match("ORCID", Sheet1.Range("1:1"), 0)
CID = WorksheetFunction.Match(True, IsEmpty(Sheet1.Range("1:1")), 0)
The reason for specifically using FIND or Match is Sheet1, used for this example, is a report which is downloaded weekly from a server. The server report has a range of cells without any empty cells in the range. The headers for each column are in row 1 and the code normally searches for the headers. The server report occasionally changes without communication of the change and the column header can be changed or the entire column is removed from the report. The users of the report do not have knowledge of VBA but have knowledge of Excel. I am retiring soon and attempting to develop instructions on how to modify the code should the column header in the server report change. In the case of the column header changing the new column header can replace the old column header. But if the column is removed from the server report, which occasionally happens, I hope and thought was to direct the user(s) to remove the text from in between the " " to search for the first empty cell in the row. Which in my tested does is not possible or I have written the code wrong. My thought was if the code finds the first empty column then it would copy empty cells, given the code places the contents of the first used row to the last used row in a column, into an array which is later paste into the report being developed. This empty column can be hidden by the user.
The following line of code represents the current method I am using for searching the server report for the headers - CID = WorksheetFunction.Match("ORCID", Sheet1.Range("1:1"), 0). ORCID is declared as a string and is assigned to one of the headers. It appears at the beginning of the sub in this manner - ORCID = "Case_ID". The instructions indicate to replace the Case_ID with the new header appearing in the server report which contains the information for the report being produced.
I have reviewed various responses to similar posts in a number of forums. My question has not been posted in any other forum.
Thank you for your time and assistance. Any assistance or suggestions are appreciated.
CID = Sheet1.Range("1:1").Find(" ", LookAt:=xlWhole)
CID = Sheet1.Range("1:1").Find("", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
CID = Sheet1.Range("1:1").Find("", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Row '
CID = WorksheetFunction.Match("ORCID", Sheet1.Range("1:1"), 0)
CID = WorksheetFunction.Match(True, IsEmpty(Sheet1.Range("1:1")), 0)
The reason for specifically using FIND or Match is Sheet1, used for this example, is a report which is downloaded weekly from a server. The server report has a range of cells without any empty cells in the range. The headers for each column are in row 1 and the code normally searches for the headers. The server report occasionally changes without communication of the change and the column header can be changed or the entire column is removed from the report. The users of the report do not have knowledge of VBA but have knowledge of Excel. I am retiring soon and attempting to develop instructions on how to modify the code should the column header in the server report change. In the case of the column header changing the new column header can replace the old column header. But if the column is removed from the server report, which occasionally happens, I hope and thought was to direct the user(s) to remove the text from in between the " " to search for the first empty cell in the row. Which in my tested does is not possible or I have written the code wrong. My thought was if the code finds the first empty column then it would copy empty cells, given the code places the contents of the first used row to the last used row in a column, into an array which is later paste into the report being developed. This empty column can be hidden by the user.
The following line of code represents the current method I am using for searching the server report for the headers - CID = WorksheetFunction.Match("ORCID", Sheet1.Range("1:1"), 0). ORCID is declared as a string and is assigned to one of the headers. It appears at the beginning of the sub in this manner - ORCID = "Case_ID". The instructions indicate to replace the Case_ID with the new header appearing in the server report which contains the information for the report being produced.
I have reviewed various responses to similar posts in a number of forums. My question has not been posted in any other forum.
Thank you for your time and assistance. Any assistance or suggestions are appreciated.
