- Joined
- Jan 2, 2013
- Messages
- 3
- Reaction score
- 0
Hello,
I've recently started to teach myself VBA and would really appreciate some advice.
Basically, I've been working on a project and have got stuck trying to work out how to get Excel to do a particular task for me. In essence, what I want my code to do is as follows:
1) Look down column A (which contains text identifiers) and select every instance where there is a given word, say 'cat'. I think I've got this bit down ok and the code is outputting a range called FoundCells with all of these cells from column A.
2) For each of the identified columns what I'm really interested in is getting data from column B, which contains purely numeric data. I think I basically want to make a range which consists of the B column cells which correspond to the rows of FoundCells.
3) Sum the contents of the range in column B
My initial thoughts were to create some kind of variable to store the data from column B and then select it and sum, so that the pseudo code would be something like:
Dim FoundCells As Range
... (these are then found)
Dim i As Integer
Dim store As Range
Dim Sum As Integer
For i = 1 To FoundCells.Count
store(i).Value = Cells(FoundCells(i).Row, FoundCells(i).Column + 1).Value
Next
Range(store).Select
Sum = Application.WorksheetFunction.Sum(Selection)
I haven't yet managed to get this kind of sequence to work. I think (possibly among other things) I'm getting confused about object types.
At this stage I'm not too concerned with making the code very efficient but more that it's an intuitive method.
Many thanks in advance!
Sam
I've recently started to teach myself VBA and would really appreciate some advice.
Basically, I've been working on a project and have got stuck trying to work out how to get Excel to do a particular task for me. In essence, what I want my code to do is as follows:
1) Look down column A (which contains text identifiers) and select every instance where there is a given word, say 'cat'. I think I've got this bit down ok and the code is outputting a range called FoundCells with all of these cells from column A.
2) For each of the identified columns what I'm really interested in is getting data from column B, which contains purely numeric data. I think I basically want to make a range which consists of the B column cells which correspond to the rows of FoundCells.
3) Sum the contents of the range in column B
My initial thoughts were to create some kind of variable to store the data from column B and then select it and sum, so that the pseudo code would be something like:
Dim FoundCells As Range
... (these are then found)
Dim i As Integer
Dim store As Range
Dim Sum As Integer
For i = 1 To FoundCells.Count
store(i).Value = Cells(FoundCells(i).Row, FoundCells(i).Column + 1).Value
Next
Range(store).Select
Sum = Application.WorksheetFunction.Sum(Selection)
I haven't yet managed to get this kind of sequence to work. I think (possibly among other things) I'm getting confused about object types.
At this stage I'm not too concerned with making the code very efficient but more that it's an intuitive method.
Many thanks in advance!
Sam
