Hello all,
I have an exercise we are doing in class to help better learn the ins and outs of VBA. This is the what we have been given to work with.
****************************************************************************************
The purpose of this exercise is to gain experience with loops and conditionals while producing a couple of useful functions. After completing this exercise, you should feel much more comfortable with looping and conditional statements.
Background
Excel’s standard MATCH and CONCATENATE functions are very useful. However, they both fall a little short:
MATCH – searches for a specified item in a range of cells, and then returns the relative position of that item in the range. However, the range of cells is limited to a single row or column. You cannot search a matrix, i.e. a rectangular range
CONCATENATE - joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items. However, this description is somewhat misleading.
We can use CONCATENATE in the following ways
=concatenate(“string 1”, “string 2”, …)
=concatenate(A1, A2, B1, B2, B3)
=concatenate(“string 1”, a1, …)
We cannot use a single range parameter with CONCATENATE. In other words, =concatenate(A1:A5) will not work.
These can be significant limitations.
The exercise consists of writing the following functions and constructing examples to illustrate/demonstrate their usage:
MatrixMatch - searches for a specified item in a range of cells, and then returns the x,y coordinates relative to an upper left corner position of (1,1). These coordinates will have to be returned as a string value
XYRow – returns the x coordinate from an (x,y) coordinate string (like the one returned by MatrixMatch) as a long
XYColumn – returns the y coordinate from an (x,y) coordinate string (like the one returned by MatrixMatch) as a long
ConcatenateRange – returns a single string result obtained by concatenating all of the values in a rectangular range. The processing will take place in row sequence. In other words, you will concatenate from left to right, top to bottom. Separate the concatenated values with commas .
XYRow and XYColumn are utility functions which will make using MatchMatrix easier to use by doing the mundane work of extracting the horizontal and vertical indexes (respectively) from an xy coordinate string.
****************************************************************************************
I know there is the Application.Match function in VBA but we were told that we can't use that and that we need to making it work with a loop.
Any help would be much appreciated as me and a couple of classmates trying to solve this and are having issues.
I have an exercise we are doing in class to help better learn the ins and outs of VBA. This is the what we have been given to work with.
****************************************************************************************
The purpose of this exercise is to gain experience with loops and conditionals while producing a couple of useful functions. After completing this exercise, you should feel much more comfortable with looping and conditional statements.
Background
Excel’s standard MATCH and CONCATENATE functions are very useful. However, they both fall a little short:
MATCH – searches for a specified item in a range of cells, and then returns the relative position of that item in the range. However, the range of cells is limited to a single row or column. You cannot search a matrix, i.e. a rectangular range
CONCATENATE - joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items. However, this description is somewhat misleading.
We can use CONCATENATE in the following ways
=concatenate(“string 1”, “string 2”, …)
=concatenate(A1, A2, B1, B2, B3)
=concatenate(“string 1”, a1, …)
We cannot use a single range parameter with CONCATENATE. In other words, =concatenate(A1:A5) will not work.
These can be significant limitations.
The exercise consists of writing the following functions and constructing examples to illustrate/demonstrate their usage:
MatrixMatch - searches for a specified item in a range of cells, and then returns the x,y coordinates relative to an upper left corner position of (1,1). These coordinates will have to be returned as a string value
XYRow – returns the x coordinate from an (x,y) coordinate string (like the one returned by MatrixMatch) as a long
XYColumn – returns the y coordinate from an (x,y) coordinate string (like the one returned by MatrixMatch) as a long
ConcatenateRange – returns a single string result obtained by concatenating all of the values in a rectangular range. The processing will take place in row sequence. In other words, you will concatenate from left to right, top to bottom. Separate the concatenated values with commas .
XYRow and XYColumn are utility functions which will make using MatchMatrix easier to use by doing the mundane work of extracting the horizontal and vertical indexes (respectively) from an xy coordinate string.
****************************************************************************************
I know there is the Application.Match function in VBA but we were told that we can't use that and that we need to making it work with a loop.
Any help would be much appreciated as me and a couple of classmates trying to solve this and are having issues.